Forum Discussion

tmau's avatar
tmau
Regular Visitor
2 years ago
Solved

Running total using a measure

Hello everyone,

first post in this wonderful community, I do apologise if some information will be missing or won't be 100% clear, I will try my best.
I would like to create a running total that originates from a measure (I have read several topics on this forum, but I can't get my head around it).

I have 2 tables and some measures, couple of filters, I have attached a picture:

 

There is one single relation (1 to many) in between VIN columns:

 

Measure of the model:

vehs = DISTINCTCOUNT('V-I'[VIN])
jobs = DISTINCTCOUNT(W[JOB_ID])
rpt = CALCULATE(DIVIDE([jobs],[vehs]))
 
As you can see from the 1st picture, the calculation of the "global" rpt is correct (5/8=0.63) and rpt per age can be plotted properly as well. If I use the quick measure to create a running total on any of columns, it works; if I use it to create the running total on the rpt, it doesn't (at least, I was not able to make it work).

I would like to have a running total of the rpt, the result should be (considering the applied filters, as per picture):
Age      RPT       RPT cumulated
3          0.67       0.67
6          1.00       1.67
9          0.50       2.17
 
Any suggestion is highly appreciated.
Thank you in advance.
Have a nice day.
Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    tmau , Hope Age from one side is used in visual

     

    Calculate(Sumx(Values('V-I'[Age]), [RPT]), filter(all('V-I'),'V-I'[Age] <= Max('V-I'[Age]) ) )

     

    or

    Calculate(Sumx(Values('V-I'[Age]), [RPT]), filter(all('V-I'[Age]),'V-I'[Age] <= Max('V-I'[Age]) ) )

     

    You can also consider the window function

    Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
    https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f

    • tmau's avatar
      tmau
      Regular Visitor
      2 years ago

      amitchandak thank you for your reply, I have tested the solution and it works (gonna mark it as accepted solution).

      Age_rpt = Calculate(Sumx(Values('V-I'[Age]), [rpt]), filter(all('V-I'),'V-I'[Age] <= Max('V-I'[Age])))

       

      One question: if wanna add a parameter (for example, to have a legend in the plot), how should I modify the formula?
      I have added "Y" in both tables.

      The formula is still good, if the "Y" column is not considered:

      Once I add the "Y" column, the values are not correct anymore:

       

      I guess it is related to the fact that there is another entity to be considered in the formula.
      Thank you in advance.