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.

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.