Dynamic Classification with two criteria

[HarishRathore]

Hi,

I want to classify towns based on their volume in different segment. Following is the example dataset:

Town Name	Brand Name	Category	Segment	Volume
Jaipur	ABC	Shampoo	Deluxe Shampoo	100
Jaipur	DEF	Shampoo	Deluxe Shampoo	50
Jaipur	GHI	Shampoo	Deluxe Shampoo	40
Jaipur	JKL	Shampoo	Deluxe Shampoo	120
Ajmer	ABC	Shampoo	Deluxe Shampoo	90
Ajmer	DEF	Shampoo	Deluxe Shampoo	130
Ajmer	GHI	Shampoo	Deluxe Shampoo	70
Ajmer	JKL	Shampoo	Deluxe Shampoo	55
Udaipur	ABC	Shampoo	Deluxe Shampoo	77
Udaipur	DEF	Shampoo	Deluxe Shampoo	35
Udaipur	GHI	Shampoo	Deluxe Shampoo	120
Udaipur	JKL	Shampoo	Deluxe Shampoo	98
Jodhpur	ABC	Shampoo	Deluxe Shampoo	80
Jodhpur	DEF	Shampoo	Deluxe Shampoo	120
Jodhpur	GHI	Shampoo	Deluxe Shampoo	95
Jodhpur	JKL	Shampoo	Deluxe Shampoo	130
Jaipur	MNO	Shampoo	Premium Shampoo	60
Jaipur	PQR	Shampoo	Premium Shampoo	45
Jaipur	STU	Shampoo	Premium Shampoo	40
Jaipur	XYZ	Shampoo	Premium Shampoo	90
Ajmer	MNO	Shampoo	Premium Shampoo	46
Ajmer	PQR	Shampoo	Premium Shampoo	55
Ajmer	STU	Shampoo	Premium Shampoo	30
Ajmer	XYZ	Shampoo	Premium Shampoo	35
Udaipur	MNO	Shampoo	Premium Shampoo	22
Udaipur	PQR	Shampoo	Premium Shampoo	55
Udaipur	STU	Shampoo	Premium Shampoo	15
Udaipur	XYZ	Shampoo	Premium Shampoo	60
Jodhpur	MNO	Shampoo	Premium Shampoo	45
Jodhpur	PQR	Shampoo	Premium Shampoo	60
Jodhpur	STU	Shampoo	Premium Shampoo	70
Jodhpur	XYZ	Shampoo	Premium Shampoo	20

So segment would be in slice or in filter. I want data to be classified based on following two parameters:

Volume Contribution	Market Share	Classification of Town
Town Contributing 80% of Volume in particular segment	>= State's Market Share	Stronghold
Town Contributing 80% of Volume in particular segment	< State's Market Share	Headroom
Town Contributing 20% of Volume in particular segment	>= State's Market Share	Emerging
Town Contributing 20% of Volume in particular segment	< State's Market Share	Small

Here State is Rajasthan so market share would of a brand in a particular segment. Each town will be classified based on their brands' volume in thier segment. I know a market share measure to be made but how do I approch this as i have 15 million rows dataset and I need help with it using DAX so that everthing would be dynamic. Any help would be appreciated.

Regards

Harish Rathore

[HarishRathore · Accepted solution]

Hi @az38 , Thanks for your effort. The criteria to classify towns is as follows ;

1. we are applying 80/20 formula here in segments, so when we are cumulating volume contribution% then top 80% ( in descending order) data will look like this 
2. 
3. so segment will always be in slicer. you can see that i have sorted data in descending order to get top % volume contributed towns. now we need to classify towns as per below criteria :
   1. If volume contribution is till 80% and market share of "My Company" in the town is >= "My Company's Total Share" in the segment then "Stronghold"
   2. If volume contribution is till 80% and market share of "My Company" in the town is < "My Company's Total Share" in the segment then "Headroom"
   3. If volume contribution is till remaining 20% and market share of "My Company" in the town is >= "My Company's Total Share" in the segment then "Emerging"
   4. If volume contribution is till remaining 20% and market share of "My Company" in the town is < "My Company's Total Share" in the segment then "Small"
4. its like 0% to 80% then 81% to 100% (since we have already sorted data in top to bottom). I want to approach this by using "DESC" formula so that it would always be dynamic whenever i am selecting any other segment. I have 15 million rows data hence request for dynamic measures.
5. I have tried my best to explain the situation. Sorry for any bad grammer or spelling mistake.

Regards

Harish

P.S. - I tried to use your link but it not working. Also in my excel link there a sheet called "Criteria". you can also go through there.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my Excel solution workbook from here.  I have written DAX measures to solve the problem.  This can very easily be imported into PowerBI Desktop but before you do so, please check the results thoroughly.

Hope this helps.

[HarishRathore · Accepted solution]

Hi @az38 , Thanks for your effort. The criteria to classify towns is as follows ;

1. we are applying 80/20 formula here in segments, so when we are cumulating volume contribution% then top 80% ( in descending order) data will look like this 
2. 
3. so segment will always be in slicer. you can see that i have sorted data in descending order to get top % volume contributed towns. now we need to classify towns as per below criteria :
   1. If volume contribution is till 80% and market share of "My Company" in the town is >= "My Company's Total Share" in the segment then "Stronghold"
   2. If volume contribution is till 80% and market share of "My Company" in the town is < "My Company's Total Share" in the segment then "Headroom"
   3. If volume contribution is till remaining 20% and market share of "My Company" in the town is >= "My Company's Total Share" in the segment then "Emerging"
   4. If volume contribution is till remaining 20% and market share of "My Company" in the town is < "My Company's Total Share" in the segment then "Small"
4. its like 0% to 80% then 81% to 100% (since we have already sorted data in top to bottom). I want to approach this by using "DESC" formula so that it would always be dynamic whenever i am selecting any other segment. I have 15 million rows data hence request for dynamic measures.
5. I have tried my best to explain the situation. Sorry for any bad grammer or spelling mistake.

Regards

Harish

P.S. - I tried to use your link but it not working. Also in my excel link there a sheet called "Criteria". you can also go through there.

[Greg_Deckler]

It seems like you want something along the lines of Dynamic ABC Classification. I realize this is not ABC classification but I think the same principles hold.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-ABC-Classification/m-p/479146#M180

[HarishRathore]

Hi Greg. Thanks for your reply but in your given Link the ABC classification is derived by fixed criteria which are some percentage but in my case it is totally different. 1st data should be sorted descending in measure itself then the classification to be done based on criteria.

Regards