cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## 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)

 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.

1 ACCEPTED SOLUTION
Super User

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

Don't forget to give thumbs up and accept this as a solution if it helped you!!!
Super User

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

Don't forget to give thumbs up and accept this as a solution if it helped you!!!