Forum Discussion
Running total based on multiple measures
Hello,
I'm having a hard time getting a running total on a measure that depends on mutiple measures. My typical pattern isn't working:
Measure 2: Calculate([Measure 1], DATESYTD(Calendar[Date])) or
Measure 2: Calculate([Measure 1], Filter(AllSelected(Calendar), Calendar[Date] <= MAX(Calendar[Date]))
Due to the sensitive nature of the data I can't share my pbix file, however I can share the output of the table to give insight into what I'm trying to do. I have the following measures calculate per week ending (as shown in the screenshot), this all calculates as expected. I have a calendar table with a 1:M relationship to my payroll table and a week ending column in the calendar table.
Measures:
1. [Avg. Hours Over 40] = (DIVIDE([Hours By Class], [Employee Count By Class], 0)) - 40
[Hours By Class] = SUM(Hours)
[Employee Count By Class] = CALCULATE(DISTINCTCOUNT(Payroll[Employee]), Filter(Payroll, Payroll[Class] IN {1,2,3,4}))
[Increase Hours RT] = CALCULATE([Increase Hours], DATESYTD('Calendar'[Date])) results in values that are incorrect (0, 14,451.43, 31,776.86, ect.)
My pattern is incorrect, but I'm struggling to come up with the correct pattern to accomplish a running total. How can I get a running total based on the output of other measures?
8 Replies
- amitchandakSuper User
Anonymous , these formulas seem fine. Can you share sample data and sample output.
- AnonymousNot applicable
Thank you for taking a look, here is what I get with this measure:
Increase Hours RT = CALCULATE([Increase Hours], DATESYTD('Calendar'[Date]))Notice how the 2nd row is not a running total as I expect it. It should be 2697.67, but it calcs to be 14,451.43.- AnonymousNot applicable
Anonymous
Based on these screens, I guess the problem is you created a running total on the [Increase Hours] measure. Try change the measure to a column if possible, If you really want a measure. Can you share the sample pbix with us, so can we make some test on it.
Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly