Cumulative sum

[AndreaCBS]

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!

[AndreaCBS]

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 

[suparnababu8]

Hi @AndreaCBS 

Can you share the pbix file with sample data with out any sensitive information with all your output reuiqrements?

[Sahir_Maharaj · Accepted solution]

Hello @AndreaCBS,

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.

[AndreaCBS]

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]

Hi @AndreaCBS 

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.

[AndreaCBS]

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]

Hi @AndreaCBS 

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.