I want to calculate the cumulative total.

[Eiji77]

I want to calculate the cumulative total.

[v-danhe-msft]

Hi @Eiji77,

Which formula do you want to calculate the cumulative total with, a calculated column or a measure?

If the former, you could refer to this formula:

Column = CALCULATE(SUM(Table1[Sales]),FILTER('Table1','Table1'[Customer]<=EARLIER(Table1[Customer])))

Result:

If the latter, you could refer to this formula:

Measure = CALCULATE(SUM(Table1[Sales]),FILTER(ALL(Table1),'Table1'[Customer]<=MAX('Table1'[Customer])))

Result:

You could also downlaod the pbix file to have a view.

https://www.dropbox.com/s/mu8hkb236ypwsa6/I%20want%20to%20calculate%20the%20cumulative%20total..pbix...

Regards,

Daniel He

[Eiji77]

Hi,Daniel. It seems that the way you taught is cumulative order by Customer name. Can you do it in descending order of sales amount?

[Ashish_Mathur]

Hi,

This should be possible to solve without creating an index column.  Please share the link from where i can download your PBI file.

[Eiji77]

https://www.dropbox.com/s/eb63y9gxcefvvaq/I%20want%20to%20calculate%20the%20cumulative%20total..pbix...

Thank you. I want to calculate the cumulative total in the customer group. It is not a customer name.

[v-danhe-msft]

Hi @Eiji77,

Based on my test, you could add index column to calculate the total running:

Sample data:

Add index coulumn in query editor:

Create calculated column or measure:

Calculated column:

Column = CALCULATE(SUM(Table1[Sales]),FILTER(Table1,'Table1'[Index]<=EARLIER('Table1'[Index])))

Measure:

Measure = CALCULATE(SUM(Table1[Sales]),FILTER(ALL(Table1),'Table1'[Index]<=MAX('Table1'[Index])))

Result:

Regards,

Danilel He

[Eiji77]

Sorry, I want to calculate the cumulative total in the customer group. It is not a customer name.

Then,I can not index it.

https://www.dropbox.com/s/eb63y9gxcefvvaq/I%20want%20to%20calculate%20the%20cumulative%20total..pbix...

[ImkeF]

You can add a filter statement that checks for equality within the group. But then to get the correct grand total, you need to add another condition:

Measure =
IF (
    ISFILTERED ( 'Customer Group'[Customer Group] ),
    CALCULATE (
        SUM ( Sales[Sales amount] ),
        FILTER (
            ALL ( 'Customer Group' ),
            'Customer Group'[Customer Name] <= MAX ( Sales[Customer Name] )
                && 'Customer Group'[Customer Group] = MAX ( 'Customer Group'[Customer Group] )
        )
    ),
    CALCULATE (
        SUM ( Sales[Sales amount] ),
        FILTER (
            ALL ( 'Customer Group' ),
            'Customer Group'[Customer Name] <= MAX ( Sales[Customer Name] )
        )
    )
)

[Eiji77]

https://www.dropbox.com/s/eb63y9gxcefvvaq/I%20want%20to%20calculate%20the%20cumulative%20total..pbix...

The result is like this.Why can not I?

[ImkeF]

My measure creates a cumulative total over your customers within their customer groups:

If this is not what you want, please specify exactly what you need by posting a (picture) of the desired table.

[Eiji77]

Sorry , I want to this result .

[ImkeF]

This would be it then:

Measure = 
    CALCULATE (
        SUM ( Sales[Sales amount] ),
        FILTER (
            ALL ( 'Customer Group' ),
            'Customer Group'[Customer Group] <= MAX ( 'Customer Group'[Customer Group] )
        )
    )

[Eiji77]

I tried, the result is this.This is different from what I want to do.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-danhe-msft]

Hi @Eiji77,

If you could not index it, you could use this measure:

Measure = CALCULATE(SUM(Table1[Sales]),FILTER(ALL(Table1),'Table1'[Sales]>=MIN('Table1'[Sales])))

Result:

Regards,

Daniel He

[Eiji77]

I try Measure in Customer Group , but This resulted in such a result.