Running total based on multiple measures

[Anonymous]

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}))

2. [Salaried Employee Count] = CALCULATE(DISTINCTCOUNT(Payroll[Employee]), FILTER(Payroll, Payroll[EarnCode] = 4))

3. [Increase Hours] = VAR _increaseHours = [Avg. Hours Over 40] * [Salaried Employee Count] RETURN IF(_increaseHours <= 0, 0, _increaseHours)

4. [Payroll Hours RT] = CALCULATE([Payroll Hours], DATESYTD('Calendar'[Date]))

The measures above all calculate correctly.  However I can't seem to get a running total for #3 [Increase Hous] figured out.  Using the same pattern of:

[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? 

[amitchandak]

@Anonymous , these formulas seem fine. Can you share sample data and sample output.

[Anonymous]

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.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Anonymous]

Here is the file - MeasureRunningTotalIssue.pbix 

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

This is really amazing, thank you for this! For my understanding and learning would you be willing to walk me through how you arrived at this solution? 

I see you surface a table using CALCULATETABLE, and then SUMMARIZE it, but I'm not fully understanding the design pattern here and how it works.

Again, thank you!

[Ashish_Mathur]

You are welcome.  Sometime later please.

[Anonymous]

@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