cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Have Market Share equal 100% with every type of Date Drill Down

I created a Market Share calculation as follows:

 

Market Share = divide(CALCULATE(SUM(Sheet1[Number of Sales])), Calculate(sum(Sheet1[Number of Sales]), all(Sheet1)))

Which creates the following if I put the 'Market Share' formula into Values in the stacked bar chart visual:

 

power bi question.PNG

 

However, I'm interested in percent of Market Share by each drill down of time. 

 

I want each date axis to have 100 percent market share. 

 

Each column would add up to 100 percent. This is what each year would look like:

 

Market Share by year.PNG

 

Then this is what Market Share for Quarter would look like:

 

Market Share by quarter.PNG

 

 

I want to be able to tell market share for a given month, year or quarter without hard coding. 

 

The question I cannot asnwer:

 

  • What percent of market share did Adventure Works have in October 2013? 

 

  • Then quickly answer, what percent of market share did Adventure Works have in October 2015? 

 

Where each Market Share measure is a factor of 100%. 

 

Right now when I run the query above, I'll get .002% for October 2013  and .0005% for October 2015 instead of 50% and 14% for each month of interest. 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Hi @jpf5046,

 

For your requirement that "want each date axis to have 100 percent market share", you should use 100% stacked column chart rather than stacked column chart. And there is no need to create a measure to calculate the percentage value. You only need to drag Sheet1[Number of Sales] into value section, add [Date] into x-axis section and add [Product] into legend section.

1.PNG

 

For your questions, "What percent of market share did Adventure Works have in October 2013", please see below examples in my test.

 

Drill down to Year-Quarter level, we can see that the percentage value which is highlighted in red,

 

85.71%=180/(180+30)

Here, 180 means the total sales of SoftWare in 2016 Quarter1, including Jan, Feb and March. 30 represents the total sales of HardWare in 2016 Quarter1.

2.PNG

 

Drill down to Year-Month level.

 

44.44%=16/(16+20)

Here, 16 means the total sale of HardWare in 2016 September

 

3.PNG

 

 

From above screenshot you provided, I cannot understand what did you mean "I'll get .002% for October 2013  and .0005% for October 2015 instead of 50% and 14% for each month of interest". For further analysis, please share your sample data and elaborate your scenario with actual screenshot.

 

Regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Highlighted
Microsoft
Microsoft

Hi @jpf5046,

 

For your requirement that "want each date axis to have 100 percent market share", you should use 100% stacked column chart rather than stacked column chart. And there is no need to create a measure to calculate the percentage value. You only need to drag Sheet1[Number of Sales] into value section, add [Date] into x-axis section and add [Product] into legend section.

1.PNG

 

For your questions, "What percent of market share did Adventure Works have in October 2013", please see below examples in my test.

 

Drill down to Year-Quarter level, we can see that the percentage value which is highlighted in red,

 

85.71%=180/(180+30)

Here, 180 means the total sales of SoftWare in 2016 Quarter1, including Jan, Feb and March. 30 represents the total sales of HardWare in 2016 Quarter1.

2.PNG

 

Drill down to Year-Month level.

 

44.44%=16/(16+20)

Here, 16 means the total sale of HardWare in 2016 September

 

3.PNG

 

 

From above screenshot you provided, I cannot understand what did you mean "I'll get .002% for October 2013  and .0005% for October 2015 instead of 50% and 14% for each month of interest". For further analysis, please share your sample data and elaborate your scenario with actual screenshot.

 

Regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted

This is exactly what I needed. It's one of the base visuals! oh man!

 

Thank you @v-yulgu-msft

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors