Forum Discussion

tmullady's avatar
tmullady
Regular Visitor
10 years ago
Solved

Running total chart

Hello,

 

I wanted to see if someone could help me create a running total chart. My fields are the following

 

DATE , RANGE, CONTRIBUTIONS

 

Any help would be greatly appreciated.

 

Thanks,

 

TIm

10 Replies

  • austinsense's avatar
    austinsense
    Impactful Individual

    Running total usually looks like this ...

     

    CALCULATE ( SUM(TABLE[Contributions]), FILTER( ALL(DATE), DATE[Date] <= MAX(DATE[Date] ) )

    • tmullady's avatar
      tmullady
      Regular Visitor

      The numbers seem to be off slightly with this.

       

       CALCULATE(SUM(Query20[NET_CONTRIBUTIONS]),FILTER(ALL(Query20[DATE]),Query20[DATE] <=MAX(Query20[DATE])))

      • Anonymous's avatar
        Anonymous
        Not applicable

        Note that in the examples the last part with the filter for dates is filtering a separate date table. You need to add another table to your data model for this kind of time intelligence.

  • Hello Everyone

     

    I am very new using Power BI and I am trying to follow your suggestions but I am still stuck with the total running values.

     

    For ACC AVGPoints I have:

    ACC AVGPoints =
          CALCULATE(
          SUM(TResponses[AVG Points]),
          FILTER(
             ALL(TResponses[Date of Activity - Valida]),
             TResponses[Date of Activity - Valida] <= MAX(TResponses[Date of Activity - Valida])),
          VALUES(TResponses[AVG Points])
    )

     

    But still, its only summing the points by the week and not accumulating total. What should I do?

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      OscJara as I pointed out earlier, the examples given here use a separate date table. tmullady's last formula was incorrect because it is referring to dates in the same table, not using a relationship to a dedicated date table as the examples showed. You're doing the same thing.

       

      In your formula, this argument:

       

      MAX(TResponses[Date of Activity - Valida])

       

      ...returns the maximum date of activity in the current filtering context, not the maximum date overall. Maybe it would help to see how the two formulas translate to plain English.

       

      Code:
      
      ACC AVGPoints = CALCULATE(
      	SUM(TResponses[AVG Points]),
      	FILTER(
      		ALL(TResponses[Date of Activity - Valida]),
      		TResponses[Date of Activity - Valida] <= MAX(TResponses[Date of Activity - Valida])
      	),
      	VALUES(TResponses[AVG Points])
      
      
      Translation:
      
      ACC AVGPoints = give me the sum of TResponses[AVG Points] for all rows that match...
      ...the set of all rows in the entire Date of Activity - Valida column...
      ...where the Date of Activity - Valida is less than or equal to the latest Date of Actvity - Valida
      in the current filter context... ...VALUES(TResponses[AVG Points]) just returns a column of all unique rows from that column
      and I don't think it really accomplishes anything here because those values are already called
      by the SUM at the beginning.

      I believe the problem is that because the filter context is coming from the OrderWeek column on the same table, you're implicitly passing another filter statement to that formula that essentially says, "Also filter TResponses to only the rows matching the current OrderWeek context on this chart" which effectively nullifies the ALL part of your formula.

       

      Read the cumulative total article linked above and look at how it's done there. The example works if you do it the way the example demonstrates.

       

  • i have plotted bar chart with running total and created a tooltip for it but i am unable to see the exact rows in tooltip as the number present on the bar.

    The tooltip  is for the orange bar having 6 number .