Hello everyone,
I'm stuck. PowerBI experts needed to help create a measure based on two different funding methods. My data has the following numeric fields: total number of hours attended, scheduled hours, FTE, LHE, and one alpha field "Funding Method" that takes two values - DE and PA.
For funding method 'DE', the ACS (average class size) will be calculated using the following formula: (FTES*525)/(LHE*30)
and for the 'PA' funding method the ACS will be calculated using (Total Attendance Hours/Cum Scheduled Hours)
What DAX should I use to create such measure? Is this even doable in Power BI?
Thank you in advance.
Cristina
Hi Cristina,
This looks more like a calculated column, rather than a measure:
=IF([Funding Method]="PA", [Attendance Hours] / [Scheduled Hours], ([FTE]*525) / ([LHE]*30) )
Hi Catalin,
While the formula works for each row it does not for the Total row. That is why it must be a measure. Please see screenshot attached...see how the Total ACS is computed by adding the ACS value of rows 1 and 2... when it should be calculated again based on the formula in which case Total ACS will be 11.63 (the correct value).
Using measures takes care of this issue.
Thanks anyway (Multumesc oricum ptr. raspunsul rapid),
Cristina
You can make a measure with the same formula, but Funding Method field should be added to pivot table to provide context for calculation.
Cheers,
Catalin
