Read pivot column filter and use switch to get right formula

[Swayzy]

Hi

So I am a bit stumped by this, I have a table that contains budget and actuals values but I also want to have budget - actuals and budget / actuals calculated in my pivot.

The layout should be like this with account on rows and Q1 to Q4 as first column context and the four measures as second coloumn context
____________________________________________________________________________Q1______________________________________Q2 etc
Account Budget Actuals Budget - Actuals Budget / Actuals
4000



How can I write in a measure to read the second column context and use that in an If "column context" = Budget then use this "formula" etc for all four measures? I am pretty new to DAX so I am not super familiar with how I go about reading the actual pivot contexts/filters etc.

 

[macfuller]

I'm not quite clear on what you want your output to look like. Do you want all 4 measures to appear for each quarter and each account? You should be able to mock something up in Excel and paste it in here as a table.

		Q1				Q2
Acct	Bud	Act	Bud-Act	Bud/Act	Bud	Act	Bud-Act	Bud/Act
4000​
4002​
4003​
etc

You'll need to create each of the 4 measures in DAX. You will also want a date table with your Q1...Q4 dates to use as a column header. Once you have those it's just a question of rearranging the table to suit your layout needs.