cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shaunwilks Member
Member

Cumulative Totals in a Line Chart with a Legend

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
1 ACCEPTED SOLUTION

Accepted Solutions
Super User I
Super User I

Re: Cumulative Totals in a Line Chart with a Legend

Hi @shaunwilks ,

 

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

Cumulative Totals in a Line Chart with a Legend.PNG

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

Aiolos Zhao

View solution in original post

7 REPLIES 7
Super User I
Super User I

Re: Cumulative Totals in a Line Chart with a Legend

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 Member
Member

Re: Cumulative Totals in a Line Chart with a Legend

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

Super User I
Super User I

Re: Cumulative Totals in a Line Chart with a Legend

Hi @shaunwilks ,

 

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

Cumulative Totals in a Line Chart with a Legend.PNG

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

Aiolos Zhao

View solution in original post

shaunwilks Member
Member

Re: Cumulative Totals in a Line Chart with a Legend

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.

Super User I
Super User I

Re: Cumulative Totals in a Line Chart with a Legend

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 Member
Member

Re: Cumulative Totals in a Line Chart with a Legend

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

 

Super User I
Super User I

Re: Cumulative Totals in a Line Chart with a Legend

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

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors