Forum Discussion
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...
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.
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
So thanks for helping Sahir_Maharaj and Anonymous