Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
Hi @az38 , Thanks for your effort. The criteria to classify towns is as follows ;
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.
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.
Hi,
You may refer to my solution here.
Hope this helps.
Hi,
Share the final dataset with the Company column and for the sample data that you share, show the exact expected result.
Hi @Ashish_Mathur , Please find revised data for better understanding;
Town Name | Brand Name | Category | Company | Segment | Volume |
Jaipur | ABC | Shampoo | My Company | Deluxe Shampoo | 100 |
Jaipur | DEF | Shampoo | Company2 | Deluxe Shampoo | 50 |
Jaipur | GHI | Shampoo | Company3 | Deluxe Shampoo | 40 |
Jaipur | JKL | Shampoo | Company4 | Deluxe Shampoo | 120 |
Ajmer | ABC | Shampoo | My Company | Deluxe Shampoo | 90 |
Ajmer | DEF | Shampoo | Company2 | Deluxe Shampoo | 130 |
Ajmer | GHI | Shampoo | Company3 | Deluxe Shampoo | 70 |
Ajmer | JKL | Shampoo | Company4 | Deluxe Shampoo | 55 |
Udaipur | ABC | Shampoo | My Company | Deluxe Shampoo | 77 |
Udaipur | DEF | Shampoo | Company2 | Deluxe Shampoo | 35 |
Udaipur | GHI | Shampoo | Company3 | Deluxe Shampoo | 120 |
Udaipur | JKL | Shampoo | Company4 | Deluxe Shampoo | 98 |
Jodhpur | ABC | Shampoo | My Company | Deluxe Shampoo | 80 |
Jodhpur | DEF | Shampoo | Company2 | Deluxe Shampoo | 120 |
Jodhpur | GHI | Shampoo | Company3 | Deluxe Shampoo | 95 |
Jodhpur | JKL | Shampoo | Company4 | Deluxe Shampoo | 130 |
Jaipur | MNO | Shampoo | My Company | Premium Shampoo | 60 |
Jaipur | PQR | Shampoo | Company2 | Premium Shampoo | 45 |
Jaipur | STU | Shampoo | Company3 | Premium Shampoo | 40 |
Jaipur | XYZ | Shampoo | Company4 | Premium Shampoo | 90 |
Ajmer | MNO | Shampoo | My Company | Premium Shampoo | 46 |
Ajmer | PQR | Shampoo | Company2 | Premium Shampoo | 55 |
Ajmer | STU | Shampoo | Company3 | Premium Shampoo | 30 |
Ajmer | XYZ | Shampoo | Company4 | Premium Shampoo | 35 |
Udaipur | MNO | Shampoo | My Company | Premium Shampoo | 22 |
Udaipur | PQR | Shampoo | Company2 | Premium Shampoo | 55 |
Udaipur | STU | Shampoo | Company3 | Premium Shampoo | 15 |
Udaipur | XYZ | Shampoo | Company4 | Premium Shampoo | 60 |
Jodhpur | MNO | Shampoo | My Company | Premium Shampoo | 45 |
Jodhpur | PQR | Shampoo | Company2 | Premium Shampoo | 60 |
Jodhpur | STU | Shampoo | Company3 | Premium Shampoo | 70 |
Jodhpur | XYZ | Shampoo | Company4 | Premium Shampoo | 20 |
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.
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.
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.
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.
Is this exactly what you want?
Will do so in a couple of days.
Hi,
You may refer to my solution here.
Hope this helps.
You are welcome.
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