Forum Discussion
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...
Hi az38 , Thanks for your effort. The criteria to classify towns is as follows ;
- 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
- 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 :
- 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"
- 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"
- 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"
- 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"
- 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.
- 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.
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.
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
Hi az38 , Thanks for your effort. The criteria to classify towns is as follows ;
- 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
- 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 :
- 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"
- 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"
- 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"
- 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"
- 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.
- 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.