- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
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 |
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Subject | Author | Posted | |
---|---|---|---|
06-10-2024 02:16 PM | |||
09-15-2024 06:34 PM | |||
04-25-2024 03:48 AM | |||
09-01-2024 07:40 PM | |||
07-16-2024 06:33 PM |