Question on Slicer and Cumulative Totals

Anonymous — Tue, 23 Nov 2021 09:10:54 GMT

Hi community, I am a new here.

I am just getting started with learning Power BI and am using this query as a learning experience to post my first ever question to the forum..

I have an issue I am working on in a large data set but created very simple version for this query. I can't seem to upload an excel file here so will just some sample rows into a table below to help give the idea, I hope that helps.

What my goal is to have a Line Chart with the 12 periods on the axis and cumulative totals as the values. Then I want to have a slicer so I can compare the different countries. I created a Dax Query to calculate the cumulative totals (which is where I suspect my error is)

Cumulative Total = CALCULATE( SUM(Sheet1[Amount]), FILTER( ALLSELECTED(Sheet1), Sheet1[Period] <= MAX(Sheet1[Period]) ) )

However now when I use my slicer I get the combined total of the different all the countries selected. Not the individual countries which is what I am after.

As I say, I suspect it is my DAX query, perhaps the filtering.

Could someone please help, and once again, I am new, please do let me know if I am posting in the wrong place. All advice and help is very welcome 🙂

Pete

An example of my dataset follows, In reality I have about 50 countries and 12 periods (for each month) but hopefully this helps to get a general idea..

Country	Period	Amount
India	1	24
India	1	51
China	1	90
Brazil	2	91
Greenland	2	100
Mongolia	3	49
China	4	27
Greenland	5	44
Brazil	5	53

Re: Question on Slicer and Cumulative Totals

amitchandak — Tue, 23 Nov 2021 13:05:04 GMT

Anonymous , best in such case is to have separate country and period table. Or at least period table,

Then have formulas like

Cumulative Total = CALCULATE( SUM(Sheet1[Amount]), FILTER( ALLSELECTED(Period), Period[Period] <= MAX(Period[Period]) ) )

Take country as legend

Re: Question on Slicer and Cumulative Totals

Anonymous — Tue, 23 Nov 2021 13:19:36 GMT

Thank you so much,

I am not sure I understand why it worked, but yes it did.

I really appreciate you taking the time to help me. Thank you !!!!

topic Question on Slicer and Cumulative Totals in DAX Commands and Tips

Question on Slicer and Cumulative Totals

Re: Question on Slicer and Cumulative Totals

Re: Question on Slicer and Cumulative Totals