Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
I am new to Power BI DAX and need help in creating Customer Category based on Revenue
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
Below is the sample data
First, it would sum all revenue of each customer per location then rank and then we have to categorise.
I am not sure how to write DAX for this.
Can someone help me on this?
Use this DAX in a calculated column of your table : Replace Table name with whatever you have named your table
Category =
VAR __total=DISTINCTCOUNT([Location])
VAR __RANK=
RANKX(Category,CALCULATE(SUM(Category[Revenue]),ALLEXCEPT(Category,Category[Location])),,DESC,DENSE)
RETURN
SWITCH(TRUE(),__RANK>=__total*.50,"Category 1",
__RANK>=__total*.20 && __RANK<__total*.50,"Category 2",
__RANK>=__total*.05 && __RANK<__total*.20,"Category 3",
__RANK<__total*.05,"Category 4")
Thanks for your reply!
Your DAX works fine but I need to treat each location separately i.e. when ranking total revenue treat each location separately. Currently, all locations are combined together and then customer categorisation is done.
Consider we have 10 location in our dataset then categorisation should be done location wise like
Location A top 50% Catgeory 1, 20%-50% to Category B....
same for Location B top 50% Catgeory 1, 20%-50% to Category B....
Right now, all locations are considered together which should not be done.
Sorry if I misunderstood anything in my previous post.
Hi @apatwal
This is a standard ABC analysis. Please refer to the file with solution https://www.dropbox.com/t/6x4VGdXMIxCow8sB
Basically you need to create 3 calculated Columns in the same following order
Incremental Revnue =
VAR CurrentReveneue =
Data[Revenue]
VAR CurrentLocation =
Data[Location]
VAR FilteredTable =
FILTER (
Data,
Data[Revenue] >= CurrentReveneue
&& Data[Location] = CurrentLocation
)
VAR Result =
SUMX (
FilteredTable,
Data[Revenue]
)
RETURN
Result
Incremental Percentage =
VAR CurrentRevenue =
Data[Revenue]
VAR CurrentLocation =
Data[Location]
VAR FilteredTable =
FILTER (
Data,
Data[Location] = CurrentLocation
)
VAR TotalRevenuePerLocation =
SUMX (
FilteredTable,
Data[Revenue]
)
VAR Result =
DIVIDE ( Data[Incremental Revnue], TotalRevenuePerLocation )
RETURN
Result
ABC Category =
SWITCH (
TRUE,
Data[Incremental Percentage] <= 0.50, "Category 1",
Data[Incremental Percentage] <= 0.70, "Category 2",
Data[Incremental Percentage] <= 0.95, "Category 3",
"Category 4"
)
Your table looks like this.
And you can use this column to create slicers or other visuals.
Please let me know if this answers your query. If so, please consider marking this reply as acceptable answer. Thank you!
Hi @apatwal ,
You want to group by location, sort by revenue within each region, and output categories, right?
Please check my measure.
Category =
VAR _total =
CALCULATE (
COUNT ( 'Table'[Location] ),
ALLEXCEPT ( 'Table', 'Table'[Location] )
)
VAR _RANK =
RANKX (
FILTER ( ALLSELECTED ( 'Table' ), [Location] = MAX ( 'Table'[Location] ) ),
CALCULATE ( SUM ( 'Table'[Revenue] ) ),
,
DESC,
DENSE
)
RETURN
SWITCH (
TRUE (),
_RANK >= _total * .50, "Category 1",
_RANK >= _total * .20
&& _RANK < _total * .50, "Category 2",
_RANK >= _total * .05
&& _RANK < _total * .20, "Category 3",
_RANK < _total * .05, "Category 4"
)
I added a few rows to your original data.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
Thanks for your reply!
You are correct but I need to create visual filter using this.
Is it possible to create calculate column here instead of measure?
Also, I saw in your screenshot provided, marked in red MUM C01 with 109 Revenue should be in Category 2 not in Category 1
Appreciate your help!
Hi @apatwal ,
Sorry I didn't read your request carefully.
Here's the calculated column.
Category =
VAR _total =
CALCULATE (
COUNT ( 'Table'[Location] ),
ALLEXCEPT ( 'Table', 'Table'[Location] )
)
VAR _RANK =
RANKX (
FILTER ( 'Table', [Location] = EARLIER ( 'Table'[Location] ) ),
CALCULATE (
SUM ( [Revenue] ),
FILTER (
'Table',
[Customer] = EARLIER ( 'Table'[Customer] )
&& [Location] = EARLIER ( 'Table'[Location] )
)
),
,
DESC,
DENSE
)
RETURN
SWITCH (
TRUE (),
_RANK >= _total * .50, "Category 1",
_RANK >= _total * .20
&& _RANK < _total * .50, "Category 2",
_RANK >= _total * .05
&& _RANK < _total * .20, "Category 3",
_RANK < _total * .05, "Category 4"
)
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
This is not working for my dataset.
It gives me value only Category 3 and Category 4
I also have some visual filters in my report does that affect this category column not sure. 😞
Just to add, we are taking sum of revenue for all dates for each customer location wise but still the customer categorisation is wrong.
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |