Market Share Calculation

DAX DAX Calculations
1

Hi All,

I have CPG data and i want to calculate the market share. Can someone help me write a dax formula for that. There are two scenarios

Scenario 1:
image

Here the formula would be Dollar sales of a specific brand divided by the dollar sale of category. The actual data is in a very similar format as its there in this image.

Scenario 2:

image

Here i want to divide the dollar sales of each brand by the category dollar sales to which it belong to.

Kindly help me achieve it.

Thanks & Regards,
Hari G

2 Likes
2

Hello @HariGanesan,

Thank You for posting your query onto the Forum.

There’re couple of questions -

How you’re getting the values for Category? While doing the sum of Brand lines, it’s nowhere close to it and same goes for scenario 2 as well.

image

image
image730×456 90.2 KB

Or are they part of the data itself? Also please share the results the way you’re expecting it to be.

Thanks and Warm Regards,
Harsh

3

Hi @Harsh,

Thank you very much for taking your time out.

These are just dummy values. Kindly ignore the numbers as such. Since I wont be able to actual numbers i had put it randomly!

Kindly help me with this!

I am expecting the result to be in an adjacent column along with each row.
one more query i have is if this is segregated by state and year will the calculation change.

Like market share for a particular brand for each state by year/any time frame

Thanks

4

Hello @HariGanesan,

Well since there’s no presence of the PBIX file, I’m using my judgement based on the data which you’ve provided in the screenshot and providing the solution.

Below are the measures alongwith the screenshot of the final results provided for the reference -

1). Scenario 1:- Each Brand is divided by the Overall Total Sales -

% Brand Share - Dollar Sales = 
DIVIDE( 
    SUM( Data[Dollar Sales] ) , 
    CALCULATE( SUM( Data[Dollar Sales] ) , 
        ALLSELECTED( Data ) ) ,
    0 )

2). Scenario 2:- Each Brand is divided by the total from their respective Category -

% Category Share - Dollar Sales = 
DIVIDE( 
    SUM( Data[Dollar Sales] ) , 
    CALCULATE( SUM( Data[Dollar Sales] ) , 
        ALLEXCEPT( Data , Data[Category] ) , 
        VALUES( Data[Category] ) ) ,
    0 )

Same method is also applicable for the calculation of Volume Sales.

Final Results
Final Results1541×295 16.2 KB

I’m also attaching the working of the PBIX file alongwith the link of the video which I created recently based on this topic as well as link of one of the thread for the reference purposes.

Hoping this meets your requirements.

Note: In order to calculate the results by different dimensions adjust the context accordingly as per the scenario.

Thanks and Warm Regards,
Harsh

Calculation of % Share - Harsh.pbix (21.0 KB)

2 Likes
5

Hi @Harsh ,

Thank you very much for taking your time out.

I have a doubt, so this dax formula what this does is takes the sum of the whole sales then divides it for each brand to give its dollar share.

My question is:

What if i had a row which gives the dollar share of whole category for any given time frame as you filter. As depicted in the image I have either Category as a row or Category A or Category B.

In scenario 1 what I essentially want to do is Take one brand dollar share and divide it by Category dollar share

In scenario 2 what I want to do is Take the dollar share of each brand and divide it by the corresponding category’s dollar share.

Why I want to do it this way is currently i am only considering few major brand and not all the other brands in the category. Now that when we sum up the dollar share of these brands it will always be less than the dollar share of the category.

In the pbix file that you have given I see there is no separate row for Category. I understand that we are taking the sum but as i explained above my use case is a bit different.

As seen in the image i have attached, I have a separate row for category data.

That way for brand A it should be like (Dollar sales of brand A/Dollar Sales of category)
for scenario 1 and the denominator will change correspondingly for scenario 2

Kindly help me with this.

Thanks & Regards,
Hari G

6

Hello @HariGanesan,

I’m neither able to understand your scenario nor the results you’re expecting.

As stated before, please share the working and the results that you’re expecting in a proper format without which it un-necesarily consumes the time of the Forum members and just involves guess work on their part.

Please share the working accordingly so that members of the forum can provide assistance in a better and efficient manner.

Thanks and Warm Regards,
Harsh

1 Like
7

Hi @HariGanesan, I noticed you didn’t provide a PBIX file. Providing one will help users and experts find a solution to your inquiry faster and better.

A perfect initial question includes all of the following:

  • A clear explanation of the problem you are experiencing

  • A mockup of the results you want to achieve

  • Your current work-in-progress PBIX file

  • Your underlying data file (to allow us to go into Power Query if necessary to transform your data and/or data model – often DAX questions really end up being data modeling solutions)

Check out this thread on Tools and Techniques for Providing PBIX Files with Your Forum Questions

Not completing your data consumes the time of Forum members trying to help out.

8

Hello @HariGanesan, we’ve noticed that no response has been received from you since July 11th.

We were waiting for your masked demo pbix file and other supporting links and details. Other users can help you more if you have a PBIX file in your query.

Due to your inactivity, we’ll be tagging this post as Solved.

Should you wish to add your masked demo pbix file and other supporting links and details, you can reopen this thread.