Forum Discussion

HarishRathore's avatar
HarishRathore
Helper II
6 years ago
Solved

Dynamic Classification with two criteria

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 Shampo...
  • HarishRathore's avatar
    HarishRathore
    6 years ago

    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 
    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's avatar
    Ashish_Mathur
    6 years ago

    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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    Hi,

    You may refer to my solution here.

    Hope this helps.

HarishRathore's avatar
HarishRathore
Helper II
6 years ago

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_40JmLk8B8BPc5NlP9vd0Vbl5aDUZwA?e=pyelnl

 

Please do revert for any query.

 

Regards

Harish Rathore

 

 

Ashish_Mathur's avatar
Ashish_Mathur
Super User
6 years ago

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's avatar
    HarishRathore
    Helper II
    6 years ago

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

     

    Regards

    Harish Rathore

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      6 years ago

      You are welcome.  Thank you for yoru kind words.

      • HarishRathore's avatar
        HarishRathore
        Helper II
        6 years ago
        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