Hello,
In a Pivot Table I have the following columns:
A = Year - Month and hidden are the expenses per category
B = Budget
C = Preliminary expenses
D = Realized expenses
E = B - (C + D)
F = the problem, lol
In column F I want a calculated field namely:
If E > 0 ; "Budget overrun" ; " "
So if I expand the pivot table, the text will appear on every line. Now, as a solution I have an extra column behind the pivot table containing a formula per line to achieve the same, but when I expand the pivot table it is no longer correct.
1) Can I do that with a calculated field? If so, how?
2) If not, can I solve this differently?
Thank you very much for the effort.
With best regards,
Onno Zwikker
Hi,
First of all, you can't add a textual value like "Budget Overrun" in the value area of a regular pivot table. That would require Power Pivot and a few DAX measures.
Secondly, you don't mention how the underlying data is structured. If the data is as shown in the first example, you could add a calculated field for the variance between budget and actual and then apply a custom format to these numbers.
If the data comes like in the second example, load it to the Data Model (Power Pivot) and add measures to separately sum the budget and actual numbers and then another measure that returns the word "Over" if actual exceeds budget, otherwise blank.
Both pivot tables look the same but are created in two totally different ways.
If this does not make sense or if you have difficulties applying either of these options, please come back here and include a file (without any confidential information) containing a representative sample of your actual data.
(Press "Attachments" below, choose a file, press "Start upload", wait until it's done and Submit Reply)
Hi, Thanks. The first option in the attachment you send me option worked fine. The way the result was presented, a custom format, was the trigger: "Over";;
I think, looking at your name you are Dutch to and living in Sweden.
Greetings,
Onno Zwikker
Glad it worked for you. And correct on both assumptions.
