Cumulative Totals in a Line Chart with a Legend

[shaunwilks]

Ive had a great read all the resources I can but I am coming up short on a solution to this one.

Have read https://www.daxpatterns.com/cumulative-total/ amongst many other pages.

Im wanting to use a Cumulative Total inside a Line Graph, BUT with a Legend.

Without the legend I have everything working well using 

Total Sales _running = CALCULATE(sum('Sales'[Net Value]), FILTER (ALLSELECTED( 'Sales'),'Sales'[Trans Date]<= MAX('Sales'[Trans Date])))

This works well when there is only ever 1 line in the Line graph. So if I filter all sales by a specific Item Code, then the line value shows correctly, if I dont filter then one line appears for total sales and it is correct also.

The problem I have is I now want to introduce a Legend for "Item Code", so I want a cumulative line in the graph for all Item Codes in this example.

Ive tried using the ALLEXCEPT in my formula but havent got the desired results. What would I add/change in this formula to have it cater for the individual Items

[Anonymous]

Hi @shaunwilks ,

Do you mean you want to ignore the filter about the "Item Code"?

If yes, please try to use below DAX:

Calculate(your measure,ALL([Item Code]))

Thanks.

Aiolos Zhao

[shaunwilks]

Thanks for the suggestion but that will result in 1 single solid line.

If you had....

ITEM...SALES...Date

A       40       01/01/2019

A       20       01/03/2019
B       20       01/06/2019
A       60       01/11/2019

C       70       01/12/2019

If I was to just use the measure I put above, with the date along the x axis, into a line graph it shows 1 single line for total sales. Its starting point is 40 in 01/01/2019 and its ending point is 210 in 01/012/2019.

If I drag the ITEM column into the legend I then want a line per item.

But when I do this, the measure I was using before shows a cumulative total for ALL Items, not specific to the cumulative total for the individual lines that represnet wach item code.

So I have three lines, that all end up in December with a value of 210.

I want the cumulative total to represent the individual Item code that each line in the line graph is.

Thanks in advance for any other assistance

[Anonymous · Accepted solution]

Hi @shaunwilks ,

I got what you want, I think below is that:

CALCULATE(sum(Table1[SALES]), FILTER(ALL(Table1[Date]),Table1[Date] <= MAX(Table1[Date])))

Aiolos Zhao

[shaunwilks]

Thanks so much for your time in helping on this one.

I have that working - The one slight hitch is that I have a Calendar table that joins to the Sales table in your sample above.

1) Could you advise what adjustments Id need to make if the Date along the x-axis was sourced from a Date Calendar?

2) It may be pushing it but could you explain how the formula you provided works. Why doenst specifying the date column work in that instance.

[Anonymous]

1) I think if you can use merge query in the power query editor, to merge the date into your sales table, then you can use the solution directly, or you can try to use the expression just change the table name. please try.

2) to be honest, i'm also new to power bi, I'm not sure whether I can explan it clearly. In my mind, if you use allselected(table), you will ignore the item code, but you want to split the cumulative by item code, so it's enough to only use all(date).

And could you please mark my reply as accepted solution? so other person will know this topic is solved.

Thanks.

Aiolos Zhao

[shaunwilks]

1) I think if you can use merge query in the power query editor, to merge the date into your sales table, then you can use the solution directly, or you can try to use the expression just change the table name. please try...

Dont want to merge as I have a date slicer across multiple pages that I want to apply.

So it has to be the Date Calendar - Ive tried everything with it as the table and dotn have it working unfortunatley.

Ill flag as solution However if anyone can tweak the formula given to achieve what I required please feel free to respond

[Anonymous]

if it doesn't work by change the table name in DAX, I think the problem is the relationship between these 2 tables, if you can give a small sample about your data including relationship key, I can try to find out the reason.

Aiolos Zhao