Forum Discussion

M_SBS_6's avatar
M_SBS_6
Helper V
2 years ago
Solved

Running Total

Hi, 

I have a running total line chart which seems to be working if my x-axis is uses the Year and Month hierarchy. I see a continued growth.

 

My legend is a Column which has grouped a Product. 

 

However, if I change the x-axis to show just the month (as I want the total for all years Jan to Dec), I lose the cumulative view, it then just gives me the total for each month. 

 

Any idea how I can change this please? 

 

My measure:

 

Run_total=

Calculate(sum(prod[amt],

Filter( Allselected (prod),

Prod[date] <= max prod[date])))

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi M_SBS_6 ,

    some_bih Thanks for your concern about this case!
    Here is my sample data:

    And I have tested your measure, but there is nothing wrong with it. Here are the test results:

    Whether I use a hierarchy of dates or not, I can have cumulative totals appearing. 
    If you are facing this situation:

    You can try to view it by clicking on this button:

    And after clicking it will change into this:



    If your problem is still not solved, it would be helpful if you could provide us with your sample data, thank you!

    Best Regards,
    Dino Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi M_SBS_6 ,

    some_bih Thanks for your concern about this case!
    Here is my sample data:

    And I have tested your measure, but there is nothing wrong with it. Here are the test results:

    Whether I use a hierarchy of dates or not, I can have cumulative totals appearing. 
    If you are facing this situation:

    You can try to view it by clicking on this button:

    And after clicking it will change into this:



    If your problem is still not solved, it would be helpful if you could provide us with your sample data, thank you!

    Best Regards,
    Dino Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • some_bih's avatar
    some_bih
    Community Champion
    2 years ago

    Hi M_SBS_6 based on your description and measure, it seems that in some of your measure DAX functin ALLSELECTED is used in definition (Allselected - you wrote above9.

    Using ALLSELECTED, according to best practice is for visual. Still, if you define measure with ALLSELECTED  and create another measure which reference first measure with ALLSELECTED  in definition, the issue like unlogical results or hard to intepred result occur.

    If above is case for your measures, when rewrite measures.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    This approach should work.

    1. Create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name by the Month number column
    2. Create a relationship (Many to One and Single) from the Date column of the Prod Table to the Date column of the Calendar Table
    3. To your visual/slicers/filters, drag any date related field from the Calendar Table
    4. Write these measures

    Total = sum(Prod[amt])

    Running total = calculate([Total],datesbetween(Calendar[date],minx(all(calendar),Calendar[date]),max(calendar[date])))

    Hope this helps.