Calculate Marketshare - Help

[GoncaloCare]

Hi. I need some help building a table with marketshare values.

So, imagine I have this table:

Year-Month	Brand	Value
Jan'21	A	45
Jan'21	B	35
Jan'21	A	76
Jan'21	C	465
Jan'21	B	4564
Feb'21	B	363
Feb'21	B	4646
Feb'21	C	564
Feb'21	C	6456
Feb'21	A	346
Mar'21	C	36345
Mar'21	A	363
Mar'21	B	6546
Mar'21	C	3456
Mar'21	A	353

And I want to calculate the marketshare of each brand in each month.

The end result needs to be something like this:

Year-Month	Brand	Value
Jan'21	A	0.023336548
Jan'21	B	0.886981678
Jan'21	C	0.089681774
Feb'21	A	0.027959596
Feb'21	B	0.404767677
Feb'21	C	0.567272727
Mar'21	A	0.01521365
Mar'21	B	0.139090156
Mar'21	C	0.845696194

To get this values, I calculated on excel as you can see in this image here .  The final table is the red circled one. And I also putted the formulas I used. 

This is to have a table with the values to build a line chart. I tried to put the vields and use "Percentage of Grand Total" with my real values, and it was not working. 

Hope you can help me!

[Jihwan_Kim · Accepted solution]

Hi, @GoncaloCare 

Please check the below picture and the sample pbix file's link down below.

Percentage by month =
IF (
ISFILTERED ( 'Calendar'[Year-Month] ),
DIVIDE (
SUM ( Data[Value] ),
CALCULATE ( SUM ( Data[Value] ), ALLSELECTED ( Brands[Brand] ) )
)
)

https://www.dropbox.com/s/czwzog2rny4zypo/goncalocarre.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

[GoncaloCare]

Hi again @Jihwan_Kim !

So, it's working great! 

I have a question. In the line chart, I have 188 Brands, but only like 30/50 are showing. Do you know why that is happening? is that a limitation?

Thanks!

[Jihwan_Kim]

Hi, @GoncaloCare 

Thank you for your feedback.

I am not sure about how is the limitation of showing how many categories (or legends) in a linechart.

I also sometimes face a similar situation as what you just described.

However, if I see over 20 lines in one line chart, I don't think it creates values or insights for readers. Or, perhaps you can try to select other visualizations, like a scatter chart for instance, if you need to show more than 20~30 categories.

Thank you.

[GoncaloCare]

Hi @Jihwan_Kim 

Thank you very much again. Sadly, it doesn't show all brands. Do you know any way to group brands? For example, in the table I provided, grouping A and C to "Other" group. 

Is it possible?

Thanks!

Best Regards.

[Jihwan_Kim]

Hi, @GoncaloCare 

Thank you for your feedback.

If you just want to group those two, you can simply create a hierarchy table.

Or, do you want to dynamically group? For instance, below 50% goes to "Others", and shows the rest? This is also doable if you have a rule about how to group those.

If it is OK with you, please share your sample pbix file's link here, and give instructions about how to group. Then I can try to look into it to come up with a more accurate solution.

Thank you.

[GoncaloCare]

Hi @Jihwan_Kim 

Thanks again for your help! 

I would love it would be possible to dynamically group. Like, if it's above 1% MS, group into "Others. 

I'm sending the raw data (I changed the names of the brands). 

Here is the link https://www.dropbox.com/s/jk3wnttwec8jevn/testeee.pbix?dl=0

[Jihwan_Kim]

Hi, @GoncaloCare 

I am not sure whether I understood your question correctly.

I assume you want to group less than 1% into Others, right? 

Please check the link down below.

Please also check the Brands Table -> I created "Others" as index number 128. 

https://www.dropbox.com/s/ox56wrttiyyzb2w/testeee.pbix?dl=0 

[GoncaloCare]

Hi @Jihwan_Kim 

You are amazing! Thank you so much, this is perfect! You really helped me! 

Kudos for you!

Best regards!

[GoncaloCare]

Hi! I will for sure try your solution! I'll reply again If I need any assistance! Thank you very much!

[amitchandak]

@GoncaloCare , Try a measure like

divide(sum(Table[Value]), calculate(sum(Table[Value]), filter(allselected(Table),Table[Year-Month] = max(Table[Year-Month]))))

or

divide(sum(Table[Value]), calculate(sum(Table[Value]), removefilters(Table[Brand])))

[GoncaloCare]

Hi! Thank you very much! I will try that solution, and If I need any assistance, I'll reply again. Thanks!