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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi Anonymous 

         

        Based on your screenshot, it looks like you followed Sahir_Maharaj's steps. However there is a small error in [TotalRevenue] measure, it was my mistake to check, here should add ALL(RevenueTable) to clear the filtering, otherwise the measure will be calculated based on the current row in the table vision instead of calculating the total value.

        1. Correct [TotalRevenu] measure.

        TotalRevenue = 
CALCULATE(SUM('Sales Invoices'[AmountInvoicedReportingCurrency]), ALL('RevenueTable'))

         2. Correct [CustomerRank] measure.

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

        Other measures remain unchanged, try again and check if the problem is solved.

         

        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.