Forum Discussion
Helper IIICustomer Categorization based on Revenue
Community ChampionHi 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
ResultCount 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
ResultCategory =
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] )
Helper IIIHi 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
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
Can this be happen the new category filter can be made which shows only classification for overall without selecting any location.
- tamerj1
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.
- 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
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-97Tcfv7ANcTwo 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
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.