Customer Categorization based on Revenue

[apatwal]

Hi,

Need to create a filter visual customer category based on Revenue.

Category are based on parent customer's revenue (which is the aggregate of all its child customers) i.e. aggregating invoice revenue at the parent customer level, rather than at the individual customer level.

Category are based on % of revenue. i.e., sort by revenue high to low, and take all the customers whose revenue makes up the top 50% of total revenue - this is Category 1.

Category 1: Top 50% of customers per location based on their total revenue

Category 2: Customers in 20% to 50% range

Category 3: Customers in 5% to 20%  range

Category 4: Bottom 5% of customers

We need to create below two filters based on above logic

Location Category : treats each location as separate

Aggregate Category : combines all customers, ignoring location

Any help on how to create DAX will be highly appreciated.

Thanks in Advance!

@tamerj1 

[tamerj1]

Hi @apatwal 
Here is the sampel file with the solution https://we.tl/t-KMt9n9cqPl

This is how the report looks like 

The measures are

Total Revenue = SUM ( 'Main data'[Revenue] )

Category Total Revenue = 
VAR RevenueByParentCustomer =
    ADDCOLUMNS (
        ALLSELECTED ( 'Main data'[Parent Name] ),
        "@ParentCustomerRevenue", [Total Revenue]
    )
VAR AllRevenue =
    CALCULATE (
        [Total Revenue],
        ALLSELECTED ( 'Main data'[Parent Name] )
    )
VAR CumulatedPercentByParentCustomer =
    ADDCOLUMNS (
        RevenueByParentCustomer,
        "@CumulatedPercentage",
        VAR CurrentTotalRevenue = [@ParentCustomerRevenue]
        VAR CumulatedRevenue =
            FILTER (
                RevenueByParentCustomer,
                [@ParentCustomerRevenue] >= CurrentTotalRevenue
            )
        VAR CumulatedSalesAmount =
            SUMX (
                CumulatedRevenue,
                [@ParentCustomerRevenue]
            )
        RETURN
            DIVIDE (
                CumulatedSalesAmount,
                AllRevenue
            )
    )
VAR ParentCustomerInCategory =
    FILTER (
        CROSSJOIN (
            CumulatedPercentByParentCustomer,
            'Categories'
        ),
        AND (
            [@CumulatedPercentage] > 'Categories'[Lower Boundary],
            [@CumulatedPercentage] <= 'Categories'[Upper Boundary]
        )
    )
VAR Result =
    CALCULATE (
        [Total Revenue],
        KEEPFILTERS ( ParentCustomerInCategory )
    )
RETURN
    Result

Count of Parent Customers = 
VAR RevenueByParentCustomer =
    ADDCOLUMNS (
        ALLSELECTED ( 'Main data'[Parent Name] ),
        "@ParentCustomerRevenue", [Total Revenue]
    )
VAR AllRevenue =
    CALCULATE (
        [Total Revenue],
        ALLSELECTED ( 'Main data'[Parent Name] )
    )
VAR CumulatedPercentByParentCustomer =
    ADDCOLUMNS (
        RevenueByParentCustomer,
        "@CumulatedPercentage",
        VAR CurrentTotalRevenue = [@ParentCustomerRevenue]
        VAR CumulatedRevenue =
            FILTER (
                RevenueByParentCustomer,
                [@ParentCustomerRevenue] >= CurrentTotalRevenue
            )
        VAR CumulatedSalesAmount =
            SUMX (
                CumulatedRevenue,
                [@ParentCustomerRevenue]
            )
        RETURN
            DIVIDE (
                CumulatedSalesAmount,
                AllRevenue
            )
    )
VAR ParentCustomerInCategory =
    FILTER (
        CROSSJOIN (
            CumulatedPercentByParentCustomer,
            'Categories'
        ),
        AND (
            [@CumulatedPercentage] > 'Categories'[Lower Boundary],
            [@CumulatedPercentage] <= 'Categories'[Upper Boundary]
        )
    )
VAR Result =
    CALCULATE (
        COUNTROWS ( VALUES ( 'Main data'[Parent Name] ) ),
        KEEPFILTERS ( ParentCustomerInCategory )
    )
RETURN
    Result

Category = 
IF (
    HASONEVALUE ( 'Main data'[Parent Name] ),
    VAR RevenueByParentCustomer =
        ADDCOLUMNS (
            ALLSELECTED ( 'Main data'[Parent Name] ),
            "@ParentCustomerRevenue", [Total Revenue]
        )
VAR AllRevenue =
    CALCULATE (
        [Total Revenue],
        ALLSELECTED ( 'Main data'[Parent Name] )
    )
    VAR CurrentRevenue = [Total Revenue]
    VAR CumulatedRevenue =
        FILTER (
            RevenueByParentCustomer,
            [@ParentCustomerRevenue] >= CurrentRevenue
        )
    VAR CumulatedTotalRevenue =
        SUMX (
            CumulatedRevenue,
            [@ParentCustomerRevenue]
        )
    VAR CurrentCumulatedPercentage =
        DIVIDE (
            CumulatedTotalRevenue,
            AllRevenue
        )
    VAR Result =
        SWITCH (
            TRUE,
            ISBLANK ( CurrentCumulatedPercentage ), BLANK (),
            CurrentCumulatedPercentage <= 0.5 || AND ( COUNTROWS ( CumulatedRevenue ) = 1, CurrentCumulatedPercentage > 0.5 ), "Category 1",
            CurrentCumulatedPercentage > 0.5 && CurrentCumulatedPercentage <= 0.8, "Category 2",
            CurrentCumulatedPercentage > 0.8 && CurrentCumulatedPercentage <= 0.95, "Category 3",
            "Category 4"
        )
    RETURN
        Result
)

Count of Customers = DISTINCTCOUNT ( 'Main data'[Customer Name] )

[apatwal]

Hi @tamerj1 

For Location A, we don't have Category 1 as customer segment. Category 1 encompasses the top 50% of revenue in other words, if Total Revenue is $1M, then catgory 1 customers would be top N that makes up the top $500K of revenue. Category 2 encompasses 20% to 50% of revenue and so on.

Also, to note : this categorisation should treat each location as separate and also, we need to built one more category where location are ignored in that which can be called as Overall categorisation.

Could you please look into this.?

[tamerj1]

Hi @apatwal 
Here is the sample file for static classifications as reuested based on data from Apr. 1st, 2021 to Mar. 31st, 2022.  https://we.tl/t-97Tcfv7ANc

Two Classifications are calculated; one per location and one global. 

[apatwal]

Hi @tamerj1 

Thanks for your reply!

I can see some blank values in Categorisation.

Also, I can see for location C, Per Location Category is not correct

Catgeory 1 encompasses all those customers whose revenue makes up the top 50% of total revenue.

Also, there is some misconfusion created for Invoice Dates. It was something like for each parent customer we need to take Last 12 months of data for categorisation. As of now, I would say to remove Invoice Dates and to only consider all revenues irrespective of dates.

Sorry for miscommunication.

[tamerj1]

@apatwal 

Updated considering all revenues (No time consideration) https://we.tl/t-IKDAi3F5OP

The gaps are normal when considering a specific time period as some customers did not buy during that period and hence they have no categorization. Usually we do the categorization per year same as we did for location so you will have the categorization per location per year. Regarding Category C, again this is a special case that will most probably disappear once applied to the full data set. Please do apply to the full data set and let me know of the issue persists.

[apatwal]

Hi @tamerj1 

I noticed that there is some issue in categorisation, and it is flip-flopping from Category 2 to Category 4 and then back again to Category 2 among parent customers when invoice revenue is in decreasing order as shown in below screenshot for Per Location category

Same for Global Category

Also, for full data set I am also facing same issue..

[tamerj1]

Hello again @apatwal 
What happens when selecting Location A is an odd case as one customer already accounts for more than 50% of the Revenue. However I had amended the code to accommodate this case (Please refer to updated code for "Category" in the original reply). Please advise regarding the overall location category.

[tamerj1]

@apatwal 

Yes  noticed that. I'll see what I can do. 
regarding the location, it is the same. If unselect the location then it will consider the classification as overall. Unless you need to show at the same report 

[apatwal]

@tamerj1 

Can this be happen the new category filter can be made which shows only classification for overall without selecting any location.

[tamerj1]

@apatwal 

Actually if no location is selected the current measure shows the category considering all locations is one location . If you one location  is selected it will show the category based on the selected location. If you select 2 locations then the selected locations bwill be treated ae one area. And so on. It is fully dynamic. If you want a seperate measure that does not change when selecting locations then please let me know. 

[apatwal]

@tamerj1

Measure can not be used as filter visual. Can it possible to create calculated column.?

I need to show Categorisation as filter visual.

[tamerj1]

@apatwal 

Actually calculated column is much simpler. But it won't be dynamic (for example in terms of date) we can consider the classification for each year separately for example but you cannot choose any time period and find the classification of the customers baes on that period. While the solution I provided it completely dynamic. (Let me know if you are interested in that)

However, as per the current approach you can use a slicer from the category table to slice both revenue and number of customers. If you are interested in other measures using this approach please let me know. Otherwise confirm shifting to the static classification with calculated column and advise if you need this classification to be for all times or foe each year. 

[apatwal]

Hi @tamerj1 

I confirm to shift to static classification with calculated column as we want to have a filter on page for customer classification that can be selected by user. We want show data based on customer classifcation.

Also is it possible that we can have two calculated column for classification, one should ignore location, while other should treat separate location.

Also, if possible can we have invoice revenue should be consider for Last 12months for ex: for parent customer A it should consider the revenue from 31st March 2022 to last 12 months.

Also, I can see I am getting only Category 1 in my data looks something is missed or I am confused here...

https://drive.google.com/file/d/1g9JbZ_tEQBs0u8L6VsdxCliAkBzHparn/view?usp=sharing

[karnold]

You should review the following DAX Patterns, I think they will lead you to a solution

Static segmentation – DAX Patterns

Dynamic segmentation – DAX Patterns

You might also be able to solve it in your data pipeline before it gets into the PBI model. Here is my Blog post that looks into different ways of doing segmentation Dynamic Segmentation – Kevin Arnold

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!