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 contribution, along with all previous customers (sorted from highest to lowest revenue), accumulates to 75% of the total revenue.Classify as "B" those customers whose contribution falls between 76% and 85%.Classify as "C" those contributing to the remaining revenue (86% and above).The issue I’m facing is building a cumulative sum that correctly assigns these rankings based on the rules outlined above (partially because i can't add a calculated column or modify the tables involved). I’ve tried different approaches, but I can’t seem to make the cumulative calculation work as expected.Any help or advice would be greatly appreciated!

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 contribution, along with all previous customers (sorted from highest to lowest revenue), accumulates to 75% of the total revenue.
Classify as "B" those customers whose contribution falls between 76% and 85%.
Classify as "C" those contributing to the remaining revenue (86% and above).

The issue I’m facing is building a cumulative sum that correctly assigns these rankings based on the rules outlined above (partially because i can't add a calculated column or modify the tables involved). I’ve tried different approaches, but I can’t seem to make the cumulative calculation work as expected.

Any help or advice would be greatly appreciated!

Need Help

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.

7 Replies

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
  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
    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.
    Cumulative sum.pbix39 KB
suparnababu8
Super User
1 year ago
Hi Anonymous

Can you share the pbix file with sample data with out any sensitive information with all your output reuiqrements?
Anonymous
Not applicable
1 year ago
I finally didi it only with this:

RevenueContribution =
DIVIDE(
SUM('Sales Invoices'[AmountInvoicedReportingCurrency]),
CALCULATE('Sales Invoices'[Amount Invoiced Reporting Currency (SI)], ALL('Customer Of Invoice'[Customer]))
)

CustomerRank =
RANKX(
ALL('Customer Of Invoice'[Customer]),
[RevenueContribution],
,
DESC
)

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

CustomerClassification =
SWITCH(
TRUE(),
[CumulativeRevenue] <= 0.75, "A",
[CumulativeRevenue] <= 0.85, "B",
"C"
)
So thanks for helping Sahir_Maharaj and Anonymous

Forum Discussion

Cumulative sum

7 Replies

Related content

cumulative sum

Cumulative SUM

cumulative sum

Cumulative Total

Cumulative sum