Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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/
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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/
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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!
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.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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.
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.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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
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
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi @Jihwan_Kim
You are amazing! Thank you so much, this is perfect! You really helped me!
Kudos for you!
Best regards!
Hi! I will for sure try your solution! I'll reply again If I need any assistance! Thank you very much!
@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])))
Hi! Thank you very much! I will try that solution, and If I need any assistance, I'll reply again. Thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |