Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
shaunwilks
Helper V
Helper V

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

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

7 REPLIES 7
AiolosZhao
Memorable Member
Memorable Member

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.