topic Re: Customer Categorization based on Revenue in DAX Commands and Tips

Customer Categorization based on Revenue

apatwal — Fri, 22 Apr 2022 06:58:35 GMT

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

Re: Customer Categorization based on Revenue

karnold — Sat, 23 Apr 2022 19:25:43 GMT

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!

Re: Customer Categorization based on Revenue

tamerj1 — Mon, 25 Apr 2022 08:07:44 GMT

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] )

Re: Customer Categorization based on Revenue

apatwal — Mon, 25 Apr 2022 07:13:13 GMT

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.?

Re: Customer Categorization based on Revenue

tamerj1 — Mon, 25 Apr 2022 07:20:54 GMT

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

Re: Customer Categorization based on Revenue

apatwal — Mon, 25 Apr 2022 07:58:37 GMT

tamerj1

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

Re: Customer Categorization based on Revenue

tamerj1 — Mon, 25 Apr 2022 08:06:56 GMT

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.

Re: Customer Categorization based on Revenue

tamerj1 — Mon, 25 Apr 2022 09:24:51 GMT

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.

Re: Customer Categorization based on Revenue

apatwal — Mon, 25 Apr 2022 11:03:26 GMT

tamerj1

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

I need to show Categorisation as filter visual.

Re: Customer Categorization based on Revenue

tamerj1 — Mon, 25 Apr 2022 11:36:16 GMT

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.

Re: Customer Categorization based on Revenue

apatwal — Mon, 25 Apr 2022 14:40:22 GMT

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

Re: Customer Categorization based on Revenue

tamerj1 — Tue, 26 Apr 2022 04:13:05 GMT

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.

Re: Customer Categorization based on Revenue

apatwal — Tue, 26 Apr 2022 05:38:53 GMT

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.

Re: Customer Categorization based on Revenue

tamerj1 — Tue, 26 Apr 2022 06:07:44 GMT

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.

Re: Customer Categorization based on Revenue

apatwal — Wed, 27 Apr 2022 06:09:46 GMT

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..