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
HarishRathore
Helper II
Helper II

Can someone please help me? I need help regarding this. Please
Anonymous
Not applicable

By the way... Can you not create a model in which you'd be able to show us what you want? YOu could then clearly demonstrate what numbers you want to see, even if you don't know the measure's formula right now. You could show us what the numbers should be in such a picture... A picture is worth a thousand words.

Best
D
Anonymous
Not applicable

Sorry, mate, but your description is completely incomprehensible to me. If I could clearly understand, what you want... I might be able to help you.

Best
D

I need to classify towns based on volume in particular segment and based on my company's market share in that particular brand. For example My company's brands are ABC and MNO. Rest are competition brands. I am sorry that I completely missed out putting company columns in the post. Now I need to classify.. for example total market share of my company of ABC brand in deluxe shampoo is 24.6%. now when you sort deluxe shampoo's volume in descending order then you would get Jodhpur at the top and jaipur at the bottom.
So when you pull salience or contribution of these town then you would get Jodhpur 30%, Ajmer 24%, Udaipur 23% and Jaipur 22%.

Now we need to segregate town between 2 parts based on top 80% and rest which is 20%. In 80% volume contributed towns, you would get Jodhpur, Ajmer, Udaipur.

Now jodhpur's ABC market share is 5.7% which is less than total market share of ABC which is 24.6%. hence we will classify Jodhpur town as Headroom town. (As I have mentioned in my post, rest towns are to be classified as per parameters).
I really hope that I have put my required comprehensively.

Please do help me as it would help me a lot in my analysis. Right now everything is manual. Regards

Hi,

Share the final dataset with the Company column and for the sample data that you share, show the exact expected result.


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

Hi @Ashish_Mathur , Please find revised data for better understanding;

Town NameBrand NameCategoryCompanySegmentVolume
JaipurABCShampooMy CompanyDeluxe Shampoo100
JaipurDEFShampooCompany2Deluxe Shampoo50
JaipurGHIShampooCompany3Deluxe Shampoo40
JaipurJKLShampooCompany4Deluxe Shampoo120
AjmerABCShampooMy CompanyDeluxe Shampoo90
AjmerDEFShampooCompany2Deluxe Shampoo130
AjmerGHIShampooCompany3Deluxe Shampoo70
AjmerJKLShampooCompany4Deluxe Shampoo55
UdaipurABCShampooMy CompanyDeluxe Shampoo77
UdaipurDEFShampooCompany2Deluxe Shampoo35
UdaipurGHIShampooCompany3Deluxe Shampoo120
UdaipurJKLShampooCompany4Deluxe Shampoo98
JodhpurABCShampooMy CompanyDeluxe Shampoo80
JodhpurDEFShampooCompany2Deluxe Shampoo120
JodhpurGHIShampooCompany3Deluxe Shampoo95
JodhpurJKLShampooCompany4Deluxe Shampoo130
JaipurMNOShampooMy CompanyPremium Shampoo60
JaipurPQRShampooCompany2Premium Shampoo45
JaipurSTUShampooCompany3Premium Shampoo40
JaipurXYZShampooCompany4Premium Shampoo90
AjmerMNOShampooMy CompanyPremium Shampoo46
AjmerPQRShampooCompany2Premium Shampoo55
AjmerSTUShampooCompany3Premium Shampoo30
AjmerXYZShampooCompany4Premium Shampoo35
UdaipurMNOShampooMy CompanyPremium Shampoo22
UdaipurPQRShampooCompany2Premium Shampoo55
UdaipurSTUShampooCompany3Premium Shampoo15
UdaipurXYZShampooCompany4Premium Shampoo60
JodhpurMNOShampooMy CompanyPremium Shampoo45
JodhpurPQRShampooCompany2Premium Shampoo60
JodhpurSTUShampooCompany3Premium Shampoo70
JodhpurXYZShampooCompany4Premium Shampoo20

Now we need to create a measure for My Company's market share in particular segment.  For example market share of ABC is now at 24.6% (which is expected to change or update). Please find link of excel file. I have tried to explain everything in the file.

 

Link for the file - https://chav5nz-my.sharepoint.com/:x:/g/personal/office883_chav5nz_onmicrosoft_com/EZy73DG7rFhNrNu_4...

 

Please do revert for any query.

 

Regards

Harish Rathore

 

 

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/

Hi @Ashish_Mathur , Many Many Thanks... Thats what I wanted... You are my superhero.... Bravo Bravo Bravo.

 

Regards

Harish Rathore

You are welcome.  Thank you for yoru kind words.


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

Hi @Ashish_Mathur, I have one query. When I am removing "Town Name" from Row labels in pivot then I am not getting "Town Classification" anymore. I have 124 towns in my original data and I want to show summary of town classification. For example what is the market share in town classification, number of towns in each town class. Basically I want to use town classification in row labels. Can you please help me????
Regards
Harish Rathore

Hi,

This is a tough one to solve because a measure cannot be used as a slicer/filter or in row/column labels.  However, i think i have a solution.  Please see the image below and let me know if this is what you are expecting.

Untitled.png


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

Hi @Ashish_Mathur, I want something like this but can we remove town names whenever we want? Or town names have to be there? I have 124 towns which would fall under Stronghold, Headroom, Emerging and small. So sometimes I will have to use only summary of town classification.

Regards

Hi,

Yes, you absolutely can.  You may now drag the "Classification of Town" field anywhere - row labels/column labels or slicers.  Here are the screenshots.  You may also remove Town names from the Pivot Table completely.

Untitled.png

 

Untitled1.png

Is this exactly what you want?


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

@Ashish_Mathur, yeah. That's what exactly I wanted. Can you please share the file and the process?

Regards

Will do so in a couple of days.


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

Ok.

Hi,

You may refer to my solution here.

Hope this helps.


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

Thanks a lot @Ashish_Mathur. Million Kudos to You.

You are welcome.


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

Hi @HarishRathore 

lets try to rock

 

1. create a measures

BrandShare = DIVIDE(CALCULATE(SUM('Table'[Volume]);ALLEXCEPT('Table';'Table'[Brand Name];'Table'[Town Name]));CALCULATE(SUM('Table'[Volume]);ALLEXCEPT('Table';'Table'[Segment])))
CompanyShare = DIVIDE(CALCULATE(SUM('Table'[Volume]);ALLEXCEPT('Table';'Table'[Company];'Table'[Segment];'Table'[Town Name]));CALCULATE(SUM('Table'[Volume]);ALLEXCEPT('Table';'Table'[Segment];'Table'[Town Name])))
TownShare = DIVIDE(CALCULATE(SUM('Table'[Volume]);ALLEXCEPT('Table';'Table'[Town Name];'Table'[Segment]));CALCULATE(SUM('Table'[Volume]);ALLEXCEPT('Table';'Table'[Segment])))

2. Create a column

TownRank = RANKX(FILTER('Table';'Table'[Segment]=EARLIER('Table'[Segment]));[TownShare])

3. Create a measure

CumulativeTown = 
var _countRows = 
CALCULATE(COUNTROWS('Table');ALLEXCEPT('Table';'Table'[Town Name];'Table'[Segment]))
RETURN
DIVIDE(CALCULATE(SUMX('Table';[TownShare]);FILTER(ALL('Table');'Table'[Segment]=SELECTEDVALUE('Table'[Segment]) && 'Table'[TownRank]<=SELECTEDVALUE('Table'[TownRank])));_countRows)

4. Finally, create your Classification measure

Classification = SWITCH(TRUE();
[CumulativeTown] < 0,8 && [CompanyShare]<[TownShare]; "Headroom";
[CumulativeTown] < 0,8 && [CompanyShare]>=[TownShare]; "Stronghold";
[CumulativeTown] >= 0,8 && [CompanyShare]>=[TownShare]; "Emerging";
[CumulativeTown] >= 0,8 && [CompanyShare]<[TownShare]; "Small";
"Undefined"
)

 

Not sure you give a correct rule how to define 80% of market with your example, it could be an issue

 

pbix-file is here https://ufile.io/zud4fv6e 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

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!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors