Table Matrix Report Total Sum Issue

[vinothkumar1990]

Hi All,

I have requirement to calculate the cumulative sales for each month with the below rows and columns.
Rows - Region and Country
Column - Mon-YYYY
Values - Sales (Sales Amount)

Sample calculation from below data,
For Asia Region, Sales amount is on Apr-24 is 3000, May-24 is 8000 (3000+5000) and Jun-24 sales amount is 15000 (3000+5000 + 7000)

Region	Country	Mon-YYYY	Sales Amount
Asia	India	Apr-24	1000
Asia	Srilanka	Apr-24	2000
Africa	South Africa	Apr-24	3000
Europe	France	Apr-24	4000
Europe	Germany	Apr-24	5000
North America	USA	Apr-24	6000
Asia	India	May-24	2000
Asia	Srilanka	May-24	3000
Africa	South Africa	May-24	4000
Europe	France	May-24	5000
Europe	Germany	May-24	6000
North America	USA	May-24	7000
Asia	India	Jun-24	3000
Asia	Srilanka	Jun-24	4000
Africa	South Africa	Jun-24	5000
Europe	France	Jun-24	6000
Europe	Germany	Jun-24	7000
North America	USA	Jun-24	8000

I created the below measure which is give me proper result at Country Level but not at Region level

Sales =

       CALCULATE(

                    SUM(Sales[Sales Amount]),

                        FILTER(ALL(Sales),

                              Sales[Mon-YYYY] <= MAX(Sales[Mon-YYYY]) &&

                              Sales[Region] = MAX(Sales[Region]) &&

                              Sales[Country] = MAX(Sales[Country])

                              )

        )

My report result:

Note - I have state field as well to include after the country. Since i want to my query in simple manager, i kept only Region and Country.

Thanks for your help in advance!
 

[Uzi2019 · Accepted solution]

Hi @vinothkumar1990 

Try below Calculated column not measure.

Sales 2 = CALCULATE (

    SUM ( 'Region Sale'[Sales Amount]),

    FILTER (

        FILTER ( 'Region Sale', 'Region Sale'[Country] = EARLIER ( 'Region Sale'[Country] ) ),

        'Region Sale'[Mon-YYYY] <= EARLIER ( 'Region Sale'[Mon-YYYY] )

    )

)

I hope this works for you.!