DAX PowerBI - Dynamic measure based on data type

Question

I have a data dump from our financial system, containing Department, GLCode, Year, Month, and Ledger (being Actual $ or Actual FTE) and the amount.

I want to display in a matrix, showing the sum of Actual $ the Actual FTE, BUT, when I drill up on the matrix to just the year, it SUMS the FTE (which is wrong).

I've managed to create a measure which averages the FTE and then multiplies it up by a distinct row count to get a result (you may notice I'm a bit lost). But that maths then breaks the $$ amount, and I don't know how to calculate a measure for the FTE one way, and the measure for the $$'s another way, but return them in the same variable!!

Sorry if confusing.

Department            GLCode         Ledger       Year      Month      Value
02.Provider Service   3000.222.101   Actual $     2016/17   01-Jul   1269.69
02.Provider Service   3000.222.101   Actual $     2016/17   02-Aug   1461.94
02.Provider Service   3000.222.101   Actual FTE   2016/17   01-Jul      0.25
02.Provider Service   3000.222.101   Actual FTE   2016/17   02-Aug      0.27

The above needs to return $2731.63 for Actual $ and 0.26 for Actual FTE

Andrey Nikolov Andrey Nikolov · Accepted Answer · 2019-10-14T06:26:41

One way to do this is to pivot your data on Ledger column to get separate Value columns for Actual $ and Actual FTE. Click Edit queries, select Ledger column and click Transform -> Pivot Column selecting Value for values column:

Then set the default summarization of Actual FTE to be Average:

Then you can add these columns to your visuals and have different summarizations - sum on Actual $ and average on Actual FTE, e.g. like this:

DAX PowerBI - Dynamic measure based on data type

1 Answers