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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago
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
HarishRathore's avatar
HarishRathore
Helper II
6 years ago
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
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    Hi,

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

    • 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

       

       

      • az38's avatar
        az38
        Community Champion
        6 years ago

        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