Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

Market Share in Respective Category

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;

 

MEASUREDAX 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

1 ACCEPTED SOLUTION

Attached here.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

14 REPLIES 14
Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur , Kindly find sample data and expected result;

 

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
JaipurABCDShampooMy CompanyPrestige Shampoo120
JaipurDEFGShampooCompany2Prestige Shampoo50
JaipurGHIKShampooCompany3Prestige Shampoo40
JaipurJKLMShampooCompany4Prestige Shampoo90
AjmerABCDShampooMy CompanyPrestige Shampoo70
AjmerDEFGShampooCompany2Prestige Shampoo10
AjmerGHIKShampooCompany3Prestige Shampoo30
AjmerJKLMShampooCompany4Prestige Shampoo60
UdaipurABCDShampooMy CompanyPrestige Shampoo80
UdaipurDEFGShampooCompany2Prestige Shampoo45
UdaipurGHIKShampooCompany3Prestige Shampoo35
UdaipurJKLMShampooCompany4Prestige Shampoo75
JodhpurABCDShampooMy CompanyPrestige Shampoo130
JodhpurDEFGShampooCompany2Prestige Shampoo55
JodhpurGHIKShampooCompany3Prestige Shampoo45
JodhpurJKLMShampooCompany4Prestige Shampoo95

 

Expected Result:

My Company's performance -

BrandVolumeMarket Share
ABC34725%
MNO17323%
ABCD40039%

 

so market share should be in respective segments.

Regards

Harish Rathore

Hi,

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

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Attached here.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

 

Thanks again. Kudos to you.

 

Regards

Harish Rathore

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.
 
MarketShare.PNG

 
 
Regards,
Harsh Nathani
 
Did I answer your question? Mark my post as a solution!
Also please appreciate with a Kudos

Hi @HarishRathore25 ,

 

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

 

Goto Relationship view and create a new Hierarchy.

 

MarketShare1.PNG

 

 

MarketShare2.PNG

 

 

Regards,

Harsh Nathani

 

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

Please appreciate with a kudos

Hi @harshnathani , 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

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.
 
MarketShare3.PNG
 
 
Regards,
Harsh Nathani
 
Did I answer your question? Mark my post as a solution!
Also please appreciate with a Kudos

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors