Forum Discussion

Simon_E's avatar
Simon_E
New Member
3 years ago

Cost by headcount

Hi all. I'm fairly new to Power BI but have a fair grasp of creating Desktop reports and Dashboards and a very basic understanding of DAX.

 

I need to create a measure to calculate costs per employee, however costs are on a daily basis, whereas headcount is a single data entry on the last day of each month. So if I create a simple Division measure, it won't calculate properly as I don't have a headcount for most expense dates. I assume I need to do a month aggregation of the expenses somehow, or perhaps expand the headcount table to include all dates (but no idea how to approach either way!).

 

Can someone recommend the best way to go about this?

 

I can go back to the Excel source data and add a column to in the expenses table before importing to Power BI, but I'd rather have a Measure if possible.

 

TIA

Si

Need Help

1 Reply

  • JoeBarry's avatar
    JoeBarry
    Solution Sage
    3 years ago

    Hi Simon_E 

     

    You will need to create Running total of Headcount

     

    HCRunningTotal = 
VAR MaxDate = MAX(HC[Date])
RETURN
CALCULATE(SUM(HC[HC]),
KEEPFILTERS(HC[Date] <= MaxDate),
ALL('HC'))

     

    Then Costs

    Costs =SUM(Costs[Amount])

     

    Cost per Employee

    CostPerEmployee = DIVIDE([Costs], [HCRunningTotal])

     

    Thanks

    Joe

    If this post helps, then please Accept it as the solution