Log In
Register
Home
July 12, 2022
Offline
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
November 8, 2013
Offline
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) )
July 12, 2022
Offline
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
1 Guest(s)
