Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape 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.
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.
@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 , 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
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 !!!!
User | Count |
---|---|
25 | |
21 | |
20 | |
13 | |
13 |
User | Count |
---|---|
40 | |
28 | |
27 | |
23 | |
21 |