Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Cumulative sum

Hi everyone, I’m trying to implement a logic where I classify customers based on their cumulative contribution to total revenue. My goal is to: Classify a customer as "A" if their revenue contribu...
Need Help
  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    1 year ago

    Hello Anonymous,

     

    Can you please try the following:

     

    1. Calculate the total revenue

    TotalRevenue = 
SUM(RevenueTable[Revenue])

    2. Calculate Each Customer's Contribution

    RevenueContribution = 
DIVIDE(SUM(RevenueTable[Revenue]), [TotalRevenue])

    3. Rank Customers by Revenue, then calculate the Cumulative Revenue

    CustomerRank = 
RANKX(
    ALL(RevenueTable[Customer]),
    SUM(RevenueTable[Revenue]),
    ,
    DESC
)

    CumulativeRevenue = 
VAR CurrentRank = [CustomerRank]
RETURN
CALCULATE(
    SUMX(
        FILTER(
            ALL(RevenueTable[Customer]),
            [CustomerRank] <= CurrentRank
        ),
        [RevenueContribution]
    )
)

    4. Classify Customers

    CustomerClassification = 
SWITCH(
    TRUE(),
    [CumulativeRevenue] <= 0.75, "A",
    [CumulativeRevenue] <= 0.85, "B",
    "C"
)

    Hope this helps.

Sahir_Maharaj's avatar
Sahir_Maharaj
Super User
1 year ago

Hello Anonymous,

 

Can you please try the following:

 

1. Calculate the total revenue

TotalRevenue = 
SUM(RevenueTable[Revenue])

2. Calculate Each Customer's Contribution

RevenueContribution = 
DIVIDE(SUM(RevenueTable[Revenue]), [TotalRevenue])

3. Rank Customers by Revenue, then calculate the Cumulative Revenue

CustomerRank = 
RANKX(
    ALL(RevenueTable[Customer]),
    SUM(RevenueTable[Revenue]),
    ,
    DESC
)

CumulativeRevenue = 
VAR CurrentRank = [CustomerRank]
RETURN
CALCULATE(
    SUMX(
        FILTER(
            ALL(RevenueTable[Customer]),
            [CustomerRank] <= CurrentRank
        ),
        [RevenueContribution]
    )
)

4. Classify Customers

CustomerClassification = 
SWITCH(
    TRUE(),
    [CumulativeRevenue] <= 0.75, "A",
    [CumulativeRevenue] <= 0.85, "B",
    "C"
)

Hope this helps.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    I tried to adapt what you sent in response to my actual try and i wrote this:

    CustomerLabel=
    VAR TotalRevenue = CALCULATE(SUM('Sales Invoices'[AmountInvoicedReportingCurrency]), ALL('Sales Invoices'))
    VAR CustomerRevenue =
      CALCULATE(
            SUM('Sales Invoices'[AmountInvoicedReportingCurrency]),
            'Customer Of Invoice'[Customer])
    VAR CustomerRank =
        RANKX(
            ALL('Customer Of Invoice'[Customer]),
            SUM('Sales Invoices'[AmountInvoicedReportingCurrency]),
            ,
            DESC
        )
    VAR CumulativeRevenue =
        CALCULATE(
            SUM('Sales Invoices'[AmountInvoicedReportingCurrency]),
            FILTER(
                ALL('Customer Of Invoice'[Customer]),
                RANKX(
                    ALL('Customer Of Invoice'[Customer]),
                    SUM('Sales Invoices'[AmountInvoicedReportingCurrency]),
                    ,
                    DESC
                ) <= CustomerRank
            )
        )
    VAR CumulativeRevenuePercentage = DIVIDE(CumulativeRevenue, TotalRevenue, 0)

    RETURN
        SWITCH(
            TRUE(),
            CumulativeRevenuePercentage <= 0.75, "A",
            CumulativeRevenuePercentage <= 0.85, "B",
            "C"
        )



    It's not working but first of all, I would like to understand if I have correctly integrated your suggestion.
    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi Anonymous 

       

      Sahir_Maharaj's approach is feasible, please follow his steps and use multiple measures instead of combining them into one.

       

      When you use SUM('Sales Invoices'[AmountInvoicedReportingCurrency]) directly, it will calculate it in the global context, not in the context of the current row. This means that it will calculate the total revenue for all customers, not for a single customer. This causes the RANKX function to fail to properly differentiate between each customer's revenue because it always sees the same total revenue value.
      So it is recommended to use the measure [CustomerRevenue] instead of directly using the formula SUM('Sales Invoices' [AmountInvoicedReportingCurrency]) as it will correctly calculate the revenue for each customer in the context of the current row.

      Also, you can refer to the pbix file I uploaded.

       

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        I dit it but the final result was this (keep in mind that i had to chage a few names of the measure suggested but the calculation were the same):