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
Anonymous
Not applicable

Question on Slicer and Cumulative Totals

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

 

2021 11 23 - Picture Example.JPG

 

 

 

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

CountryPeriodAmount
India124
India151
China190
Brazil291
Greenland2100
Mongolia349
China427
Greenland544
Brazil553

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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

 

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@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

 

Anonymous
Not applicable

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

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.

Top Solution Authors