topic Re: Dynamic Classification with two criteria in DAX Commands and Tips

Dynamic Classification with two criteria

HarishRathore — Tue, 25 Feb 2020 02:54:25 GMT

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

Re: Dynamic Classification with two criteria

Greg_Deckler — Tue, 25 Feb 2020 03:03:37 GMT

It seems like you want something along the lines of Dynamic ABC Classification. I realize this is not ABC classification but I think the same principles hold.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-ABC-Classification/m-p/479146#M180

Re: Dynamic Classification with two criteria

HarishRathore — Tue, 25 Feb 2020 04:40:52 GMT

Hi Greg. Thanks for your reply but in your given Link the ABC classification is derived by fixed criteria which are some percentage but in my case it is totally different. 1st data should be sorted descending in measure itself then the classification to be done based on criteria.

Regards

Re: Dynamic Classification with two criteria

HarishRathore — Wed, 26 Feb 2020 01:36:48 GMT

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

Re: Dynamic Classification with two criteria

Anonymous — Wed, 26 Feb 2020 02:01:33 GMT

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

Re: Dynamic Classification with two criteria

Anonymous — Wed, 26 Feb 2020 02:04:44 GMT

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

Re: Dynamic Classification with two criteria

HarishRathore — Wed, 26 Feb 2020 04:48:40 GMT

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

Re: Dynamic Classification with two criteria

Ashish_Mathur — Wed, 26 Feb 2020 04:57:32 GMT

Hi,

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

Re: Dynamic Classification with two criteria

HarishRathore — Wed, 26 Feb 2020 16:40:48 GMT

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

Please do revert for any query.

Regards

Harish Rathore

Re: Dynamic Classification with two criteria

az38 — Wed, 26 Feb 2020 18:03:56 GMT

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

Re: Dynamic Classification with two criteria

HarishRathore — Thu, 27 Feb 2020 02:34:50 GMT

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 :
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"
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.

Re: Dynamic Classification with two criteria

Ashish_Mathur — Thu, 27 Feb 2020 12:05:53 GMT

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.

Re: Dynamic Classification with two criteria

HarishRathore — Thu, 27 Feb 2020 16:41:53 GMT

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

Regards

Harish Rathore

Re: Dynamic Classification with two criteria

Ashish_Mathur — Thu, 27 Feb 2020 23:23:37 GMT

You are welcome. Thank you for yoru kind words.

Re: Dynamic Classification with two criteria

HarishRathore — Thu, 05 Mar 2020 08:47:28 GMT

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

Re: Dynamic Classification with two criteria

Ashish_Mathur — Thu, 05 Mar 2020 12:27:09 GMT

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.

Re: Dynamic Classification with two criteria

HarishRathore — Thu, 05 Mar 2020 13:47:31 GMT

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

Re: Dynamic Classification with two criteria

Ashish_Mathur — Fri, 06 Mar 2020 00:08:32 GMT

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?

Re: Dynamic Classification with two criteria

HarishRathore — Fri, 06 Mar 2020 01:25:57 GMT

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

Regards

Re: Dynamic Classification with two criteria

Ashish_Mathur — Fri, 06 Mar 2020 01:32:06 GMT

Will do so in a couple of days.