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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

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

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
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
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.