How to create measure to calculate Expenses per Staff Month?

[seahsteph]

I like to create a measure to calculate Expenses per Staff Month. Example: T&E divided by Staff Months. Morale divided by Staff Months. 

T&E, Morale and Staff Months are under "Expense Category" column. 

Their corresponding values are under "Value" column.

There are other slicer included such as Budget Owner, Version, Quarter.

Can someone enlighten me on how to create the formula pls?

In Excel, it will be a straightforward "=SUMIFS([Value],[Budget Owner],cell A1,[Version],cell B1,[Quarter],cell C2,[Expense Category],cell A2) / SUMIFS([Value],[Budget Owner],cell A1,[Version],cell B1,[Quarter],cell C2,[Expense Category],"Staff Month")

[v-yueyunzh-msft]

Hi , @seahsteph 

According to your description, you want to get the " Expenses per Staff Month. Example: T&E divided by Staff Months".

I have no sample data as yours, you can try to use this dax code to try if it can meet your need:

Measure = DIVIDE(
        SUM('Table'[Value]),
        CALCULATE( SUM('Table'[Value]), 'Table'[Expense Category] = "Staff Month")   
)

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[seahsteph]

Hi Aniya,

I tried the DAX code and it didn't work. Can't seem to pick up the Numerator correctly. Also tried using SUMX but failed.

I'm attaching here the sample data, and desired output sample. Please take a look and let me know how I can adjust the DAX code to correctly calculate "Expenses per Staff Month". Example: T&E divided by Staff Month.

Link to sample data, and desired output sample

Secondary link (in case the above doesn't work)

[v-yueyunzh-msft · Accepted solution]

Hi , @seahsteph 

Thanks for your quick reponse and your sample pbix and the end result !

For your need , you need to create two matrix measure and you want to get the right total in the visual.

We can create this measures:

Staff Sum of Value = CALCULATE( SUM(REPORT_QTR[Value]) , 'REPORT_QTR'[Top Expense Category]="Staff Month")

Staff Measure($/SM)-Right Total = SUMX( ADDCOLUMNS( CROSSJOIN({MAX('REPORT_QTR'[Top Expense Category])}, SUMMARIZE('REPORT_QTR','REPORT_QTR'[Version],'REPORT_QTR'[Qtr])) , "v" , [Staff Measure($/SM)]) , [v])

Staff Measure($/SM) = DIVIDE( CALCULATE( SUM(REPORT_QTR[Value]) , 'REPORT_QTR'[Top Expense Category]="Staff Month") , CALCULATE( SUM(REPORT_QTR[Value]) , 'REPORT_QTR'[Top Expense Category]="Staff Month"))

Above Measure = var _t = ADDCOLUMNS(CROSSJOIN( {"Staff Month"}, SUMMARIZE('REPORT_QTR','REPORT_QTR'[Version],'REPORT_QTR'[Qtr])), "v" , CALCULATE( SUM('REPORT_QTR'[Value]),'REPORT_QTR'[Controllable]="Controllable_Feon",TREATAS({[Value]},'REPORT_QTR'[Top Expense Category]) ,ALLSELECTED('REPORT_QTR') ))
return
DIVIDE( SUM(REPORT_QTR[Value]),SUMX(_t , [v]))

Above Measure -Right Total = SUMX( ADDCOLUMNS( CROSSJOIN(VALUES('REPORT_QTR'[Top Expense Category]), SUMMARIZE('REPORT_QTR','REPORT_QTR'[Version],REPORT_QTR[Qtr])) , "v" , [Above Measure]) , [v])

Then we can put this measures on the visual and we can get the result as follows:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[seahsteph]

Hi Aniya,

I just realised the Expenses per Staff Month ("Above Measure-Right Total") does not show up correctly in the TOTAL column.

It shows a Sum across Quarters. Instead of Full Year Total Expense / Full Year Total Staff Months.

E.g. FY T&E / Staff month is currently $1,424 (sum across quarters).

But it should be $355 (Total T&E $104,004 / Total Staff Months 293 = $355).

Could you advise if the DAX code should be adjusted? Or is there a way to adjust TOTAL column to an Average across Quarters instead of Sum across Quarters.

[v-yueyunzh-msft]

Hi , @seahsteph 

Thanks for your quick response!

You can try to modify the measure to this and we do not need to use the [Abouve Measure-Right Total]:

Abouve -2 = IF( HASONEVALUE('REPORT_QTR'[Qtr])  ,[Above Measure] ,DIVIDE( SUM('REPORT_QTR'[Value]) , CALCULATE( SUM(REPORT_QTR[Value]) , 'REPORT_QTR'[Top Expense Category]="Staff Month", 'REPORT_QTR'[Controllable]="Controllable_Feon", ALLSELECTED('REPORT_QTR'))))

Then we can get the result:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[seahsteph]

Do you have any issues with Column showing "Version" and "Qtr"?

See below snapshot... T&E $/Staff Month gets to $177 (instead of $355).

[v-yueyunzh-msft]

Hi , @seahsteph 

Thanks for your quick response!In your screen , it seems you clear the version fitler.

Can you try to use this dax code:

Abouve -2 = IF( HASONEVALUE('REPORT_QTR'[Qtr])  ,[Above Measure] ,DIVIDE( SUM('REPORT_QTR'[Value]) , CALCULATE( SUM(REPORT_QTR[Value]) , 'REPORT_QTR'[Top Expense Category]="Staff Month", 'REPORT_QTR'[Controllable]="Controllable_Feon",'REPORT_QTR'[Version]="Final Plan", ALLSELECTED('REPORT_QTR'))))

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[seahsteph]

Thanks Aniya.

Will need your help to include Version filter. In my mock-up, I will like that to be flexi. I.e. Could be "Final Plan", "Final Actual", "Final BTG".

[v-yueyunzh-msft]

Hi,  @seahsteph 

Thanks for your quick response!

And for your pbix file , it seems in the start you use the "Filter on this visual" for the [Version] fields.

And you want to filter the [Version] , i recommend you to use the slicer instead of the "Filter on this visual".

For this , it can not be get in dax.

So you need to clear the [Version] filter in it .

And then you can try to update the [Above-2] measure to test it may work?:

Abouve -2 = IF( HASONEVALUE('REPORT_QTR'[Qtr]) ,[Above Measure] ,DIVIDE( SUM('REPORT_QTR'[Value]) , CALCULATE( SUM(REPORT_QTR[Value]) , 'REPORT_QTR'[Top Expense Category]="Staff Month", 'REPORT_QTR'[Controllable]="Controllable_Feon",'REPORT_QTR'[Version]=MAX('REPORT_QTR'[Version]), ALLSELECTED('REPORT_QTR')))) 

And filter [Version] here, your value has not changed a lot, Staff month is always 293-295, and the value of visual above is also very similar. Maybe I misunderstood what you meant? If this still doesn't solve your problem, can you describe your current data and your desired end result in detail, and resend me a pbix to me to test ?

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly