cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HarishRathore
Helper II
Helper II

Dynamic Classification with two criteria

Hi,

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

Town NameBrand NameCategorySegmentVolume
JaipurABCShampooDeluxe Shampoo100
JaipurDEFShampooDeluxe Shampoo50
JaipurGHIShampooDeluxe Shampoo40
JaipurJKLShampooDeluxe Shampoo120
AjmerABCShampooDeluxe Shampoo90
AjmerDEFShampooDeluxe Shampoo130
AjmerGHIShampooDeluxe Shampoo70
AjmerJKLShampooDeluxe Shampoo55
UdaipurABCShampooDeluxe Shampoo77
UdaipurDEFShampooDeluxe Shampoo35
UdaipurGHIShampooDeluxe Shampoo120
UdaipurJKLShampooDeluxe Shampoo98
JodhpurABCShampooDeluxe Shampoo80
JodhpurDEFShampooDeluxe Shampoo120
JodhpurGHIShampooDeluxe Shampoo95
JodhpurJKLShampooDeluxe Shampoo130
JaipurMNOShampooPremium Shampoo60
JaipurPQRShampooPremium Shampoo45
JaipurSTUShampooPremium Shampoo40
JaipurXYZShampooPremium Shampoo90
AjmerMNOShampooPremium Shampoo46
AjmerPQRShampooPremium Shampoo55
AjmerSTUShampooPremium Shampoo30
AjmerXYZShampooPremium Shampoo35
UdaipurMNOShampooPremium Shampoo22
UdaipurPQRShampooPremium Shampoo55
UdaipurSTUShampooPremium Shampoo15
UdaipurXYZShampooPremium Shampoo60
JodhpurMNOShampooPremium Shampoo45
JodhpurPQRShampooPremium Shampoo60
JodhpurSTUShampooPremium Shampoo70
JodhpurXYZShampooPremium Shampoo20

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

Volume ContributionMarket ShareClassification of Town
Town Contributing 80% of Volume in particular segment>= State's Market ShareStronghold
Town Contributing 80% of Volume in particular segment< State's Market ShareHeadroom
Town Contributing 20% of Volume in particular segment>= State's Market ShareEmerging
Town Contributing 20% of Volume in particular segment< State's Market ShareSmall

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

 

 

3 ACCEPTED SOLUTIONS

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

View solution in original post

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Hi,

You may refer to my solution here.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

23 REPLIES 23

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. Capture2.PNG
  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
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors