Cumulative % Total in Canvas

[BenediktW]

Hello together,

I have a datamodel, which contains sales and customers. In the visualisations I have a table with sales by customers. 

Now I would like to calculate a running cumulative total for the prozentual sales per customer based on the total sales.

This I tried to do with an measure, as this table sales per customer should be filterable (e.g. choose different years).

This formular was working best for me so far, but I just get the % sales per customer based on total sales. and not the running total. Result should be something like this: 

Customers	Umsatz		Cumulative % Total
Customer1	x	4,30%	4,30%
Customer2	y	2%	6,30%
Customer3	z	1,50%	7,80%

VAR CurrentCustomer = MAX('myTable'[Customer])

VAR TotalSales = CALCULATE(SUM('mytable'[Sales]), ALLSELECTED('mytable'))

VAR CustomerSales = CALCULATE(SUM('mytable'[Sales]), ALLEXCEPT('myTable', 'myTable'[Customer]))

VAR CumulativeSales =

    CALCULATE(

        SUM('mytable'[Sales]),

        FILTER(

            ALLSELECTED('myTable'[Customer]),

            RANKX(ALL('myTable'[Customer]), SUM('mytable'[Sales]), , DESC) <= RANKX(ALL('myTable'[Customer]), SUM('mytable'[Sales]), , DESC)

                && 'myTable'[Customer] <= CurrentCustomer

        )

    )

RETURN

DIVIDE(CumulativeSales, TotalSales)

[Anonymous · Accepted solution]

Hi @BenediktW 

Based on the code you provided, you seem to be calculating the total sales and sales per customer correctly.

However, accumulating sales seems to be the problem. To calculate the running cumulative percentage, you need to make sure that the cumulative sales are calculating the cumulative values correctly in the table.

I made some simple changes to your code.

Here's some dummy data.

"Table"

Modified code:

Cumulative % Total = 
var currentCustomer = MAX('Table'[Customers])
var TotalSales = 
    CALCULATE(
        SUM('Table'[Sales]), 
        ALLSELECTED('Table')
    )
var CumulativeSales = 
    CALCULATE(
        SUM('Table'[Sales]), 
        FILTER(
            ALLSELECTED('Table'), 
            'Table'[Customers] <= currentCustomer
        )
    )
RETURN DIVIDE(CumulativeSales, TotalSales)

 Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataInsights]

@BenediktW,

Have you tried the new preview feature Visual Calculations? They provide a simpler way of calculating things like cumulative totals. You'll need to enable this preview feature in Power BI Desktop.

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-visual-calculations-overview