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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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
1 Guest(s)
