Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

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? 


8 Replies

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

    • Anonymous's avatar
      Anonymous
      Not 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.
       
      • Anonymous's avatar
        Anonymous
        Not 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