Forum Discussion

vinothkumar1990's avatar
vinothkumar1990
Icon for Helper II rankHelper II
2 years ago
Solved

Table Matrix Report Total Sum Issue

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)

 

RegionCountryMon-YYYYSales Amount
AsiaIndiaApr-241000
AsiaSrilankaApr-242000
AfricaSouth AfricaApr-243000
EuropeFranceApr-244000
EuropeGermanyApr-245000

North America		USAApr-246000
AsiaIndiaMay-242000
AsiaSrilankaMay-243000
AfricaSouth AfricaMay-244000
EuropeFranceMay-245000
EuropeGermanyMay-246000

North America		USAMay-247000
AsiaIndiaJun-243000
AsiaSrilankaJun-244000
AfricaSouth AfricaJun-245000
EuropeFranceJun-246000
EuropeGermanyJun-247000

North America		USAJun-248000

 

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!
 

Need Help
  • Uzi2019's avatar
    Uzi2019
    2 years ago

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

     

1 Reply

  • Uzi2019's avatar
    Uzi2019
    Icon for Community Champion rankCommunity Champion
    2 years ago

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