Forum Discussion

BenediktW's avatar
BenediktW
Icon for Advocate I rankAdvocate I
2 years ago
Solved

Cumulative % Total in Canvas

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: 

CustomersUmsatz Cumulative % Total
Customer1x4,30%4,30%
Customer2y2%6,30%
Customer3z1,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)
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.