topic Re: Market Share in Respective Category in DAX Commands and Tips

Market Share in Respective Category

HarishRathore25 — Tue, 14 Apr 2020 08:18:35 GMT

Hi,

I have a dataset which is well modelled. I am trying to get brands market share in their respective brands; following are measures which i have already created;

MEASURE	DAX Expression
Volume	= SUM ( SalesData[Depletion] )
Segment Vol	= CALCULATE ( [Volume], ALLEXCEPT ( 'ProductMaster', 'ProductMaster'[Segment Short Desc], 'ProductMaster'[Operating Segment Y/N], 'ProductMaster'[Parent Product Type] ) )
MS%	= IFERROR ( [Volume] / [Segment Vol], BLANK () )
Volume My Company	= CALCULATE ( [Volume], ProductMaster[Company Short Name] = "My Company" )
My Company MS%	= IFERROR ( [Volume My Company] / [Segment Vol], BLANK () )

everything is working fine the only thing is when I am putting my company's brand in Table in power bi and select volume and market share measures then i should be getting market share % of a brand in their respective segment and not market share of total segment vol. Please help me in this regards.

Regards

Harish Rathore

Re: Market Share in Respective Category

Ashish_Mathur — Wed, 15 Apr 2020 02:22:36 GMT

Hi,

Share some data and show the exact result that you are expecting.

Re: Market Share in Respective Category

HarishRathore25 — Wed, 15 Apr 2020 03:56:46 GMT

Hi Ashish_Mathur , Kindly find sample data and expected result;

Town NameBrand NameCategoryCompanySegmentVolume

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
Jaipur	ABCD	Shampoo	My Company	Prestige Shampoo	120
Jaipur	DEFG	Shampoo	Company2	Prestige Shampoo	50
Jaipur	GHIK	Shampoo	Company3	Prestige Shampoo	40
Jaipur	JKLM	Shampoo	Company4	Prestige Shampoo	90
Ajmer	ABCD	Shampoo	My Company	Prestige Shampoo	70
Ajmer	DEFG	Shampoo	Company2	Prestige Shampoo	10
Ajmer	GHIK	Shampoo	Company3	Prestige Shampoo	30
Ajmer	JKLM	Shampoo	Company4	Prestige Shampoo	60
Udaipur	ABCD	Shampoo	My Company	Prestige Shampoo	80
Udaipur	DEFG	Shampoo	Company2	Prestige Shampoo	45
Udaipur	GHIK	Shampoo	Company3	Prestige Shampoo	35
Udaipur	JKLM	Shampoo	Company4	Prestige Shampoo	75
Jodhpur	ABCD	Shampoo	My Company	Prestige Shampoo	130
Jodhpur	DEFG	Shampoo	Company2	Prestige Shampoo	55
Jodhpur	GHIK	Shampoo	Company3	Prestige Shampoo	45
Jodhpur	JKLM	Shampoo	Company4	Prestige Shampoo	95

Expected Result:

My Company's performance -

Brand	Volume	Market Share
ABC	347	25%
MNO	173	23%
ABCD	400	39%

so market share should be in respective segments.

Regards

Harish Rathore

Re: Market Share in Respective Category

Anonymous — Wed, 15 Apr 2020 09:39:09 GMT

Hi HarishRathore25 ,

1. Create a measure for Total Volume

Total VOlume = SUM(Data4[Volume])

2. Create a measure for Volume by Brand.

Volume by Brand Name = CALCULATE([Total VOlume],ALLEXCEPT(Data4,Data4[Brand Name]))

3. Create a measure for Volume by Segment

Volume by Segment Name = CALCULATE([Total VOlume],ALLEXCEPT(Data4,Data4[Segment]))

4. Create a measure for Market Share

Market Share = ([Volume by Brand Name]/[Volume by Segment Name])

You can use a matrix to display your values. Put row subtotals as off.

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution!

Also please appreciate with a Kudos

Re: Market Share in Respective Category

Anonymous — Wed, 15 Apr 2020 09:53:58 GMT

Hi HarishRathore25 ,

Also, One more way incase you want to show it in a table.

Goto Relationship view and create a new Hierarchy.

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution!

Please appreciate with a kudos

Re: Market Share in Respective Category

HarishRathore25 — Wed, 15 Apr 2020 12:11:50 GMT

Hi Anonymous , Thanks for your efforst but do i have to select segment name as well in order to get market share %?

Also, i only want to select my company's brand, volume and market share. See, i want to have a view of target vs actual and along with that i need to show market share of those brand only. by doing this i will be able to show perfromance dashboard.

Regards

Harish Rathore

Re: Market Share in Respective Category

Anonymous — Wed, 15 Apr 2020 14:48:29 GMT

Dear HarishRathore25 ,

Since the use ws not very clear, it really provides value to inform viewers about the Brand name and the Segment Market Share. Also, since you are using measures, you will need to use the segment name as the data is filtered by both brand and segment name as the data is filtered based on this.

Nevertheless, if you do not want to include the Segment Name, here is what you can do.

1. Create Calculated Column:

Volume by Brand Name_Col = CALCULATE([Total VOlume],ALLEXCEPT(Data4,Data4[Brand Name]))

2. Create Calculated Column:

Volume by Brand Name_Col = CALCULATE([Total VOlume],ALLEXCEPT(Data4,Data4[Brand Name]))

3. Create Calculated Column:

MS = DIVIDE(Data4[Volume by Brand Name_Col],Data4[Volume By Segment_Col],0)

Pull a table and show these values.

Since you only want to show you company details. In the Filter Pane, drag Company from Field area and Select Value as 'My Company'.

This will show data as you expected in the question above.

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution!

Also please appreciate with a Kudos

Re: Market Share in Respective Category

Ashish_Mathur — Thu, 16 Apr 2020 04:24:19 GMT

Hi,

You may download my PBI file from here.

Hope this helps.

Re: Market Share in Respective Category

Ashish_Mathur — Thu, 16 Apr 2020 04:43:37 GMT

Hi,

If you do not want to frag Brand in the visual, then refer to this file.

Hope this helps.

Re: Market Share in Respective Category

HarishRathore25 — Thu, 16 Apr 2020 12:31:02 GMT

Hi Ashish_Mathur , I am not able to download both of your files. Have tried to download on Chrome, IE and Edge.

Can you please share some other sources or links to download the file.

Regards

Harish Rathore

Re: Market Share in Respective Category

Ashish_Mathur — Thu, 16 Apr 2020 12:38:34 GMT

Attached here.

Re: Market Share in Respective Category

HarishRathore25 — Thu, 16 Apr 2020 17:03:25 GMT

Hi Ashish_Mathur , Thanks for your solution. Its perfectly working fine, but i do have one query; in measure of "Total volume of segment" you have placed ALL(Data) at the end of the measure. I have one "SalesData" and one "ProductMaster" table in my model, my measure is like this :

IF(HASONEVALUE(ProductMaster[Brand Short Desc]),

CALCULATE([Volume],

FILTER(ALLEXCEPT(ProductMaster,ProductMaster[Segment Short Desc],ProductMaster[Operating Segment Y/N],ProductMaster[Parent Product Type]),

VALUES(ProductMaster[Segment Short Desc])=[Segment of the Brand])),

CALCULATE([Volume],ALL(ProductMaster)))

Is it right? I am not getting 100% when i am manually calculating market share of brands. Kindly help.

Regards

Harish Rathore

Re: Market Share in Respective Category

Ashish_Mathur — Fri, 17 Apr 2020 01:35:00 GMT

Hi,

Please share your exact data structure/layout/tables. WIthout all information, i cannot help. Share your PBI file with dummy data but with the relevant tables so that i can offer a working solution. Be very clear of which fields from which tables do you want to drag into the final table/matrix visual.

Re: Market Share in Respective Category

HarishRathore25 — Mon, 20 Apr 2020 09:05:56 GMT

Thanks Ashish_Mathur for your solution. Its perfectly working with my requirements.

Thanks again. Kudos to you.

Regards

Harish Rathore

Re: Market Share in Respective Category

Ashish_Mathur — Mon, 20 Apr 2020 09:17:00 GMT

You are welcome.