Question on Slicer and Cumulative Totals

[Anonymous]

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

[amitchandak · Accepted 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]

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