Reply
avatar user
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

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
avatar user
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 !!!!

avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

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

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)