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 all,
I have seen simliar topics discussed, but could not figure out the right solution for myself.
So please bear with me, I am a beginner.
I created a dummy file to keep things simple. I bassically need to show this stacked column chart below as it is, but the values within the bars should be percentages (to compare market share developments). I cannot just switch to the 100% stacked bar chart, because the size of the bars matter as well.
Plus, the chart should adapt to variuos Features from Slider.
Dummy Numbers:
Time Period | Sales Channel | Drive | Brand | Sales EUR |
2019 | online | with battery | Competitor A | 5 € |
2019 | online | wo battery | Competitor A | 3 € |
2019 | offline | with battery | Competitor A | 7 € |
2019 | offline | wo battery | Competitor A | 4 € |
2019 | online | with battery | Competitor B | 7 € |
2019 | online | wo battery | Competitor B | 8 € |
2019 | offline | with battery | Competitor B | 9 € |
2019 | offline | wo battery | Competitor B | 2 € |
2019 | online | with battery | Competitor C | 3 € |
2019 | online | wo battery | Competitor C | 6 € |
2019 | offline | with battery | Competitor C | 4 € |
2019 | offline | wo battery | Competitor C | 8 € |
2020 | online | with battery | Competitor A | 3 € |
2020 | online | wo battery | Competitor A | 6 € |
2020 | offline | with battery | Competitor A | 5 € |
2020 | offline | wo battery | Competitor A | 9 € |
2020 | online | with battery | Competitor B | 1 € |
2020 | online | wo battery | Competitor B | 7 € |
2020 | offline | with battery | Competitor B | 2 € |
2020 | offline | wo battery | Competitor B | 4 € |
2020 | online | with battery | Competitor C | 6 € |
2020 | online | wo battery | Competitor C | 8 € |
2020 | offline | with battery | Competitor C | 8 € |
2020 | offline | wo battery | Competitor C | 4 € |
Thank you very much for any help!
Hi, @Anonymous ;
According to your description, you could use Line and stacked coulmn chart, and then add Sales EUR fields to line values. as follows:
1.change it to Line and stacked coulmn chart
2.Adjust the display
3.The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-yalanwu-msft ,
this is very close to what I need thank you!
Is there a possibility that the bars have different size according to their total value?
Hi, @Anonymous ;
This is difficult to achieve, or you try to use tooltip, such as:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-yalanwu-msft ,
ok thank you for your efforts. I think I ahve to use tooltips or find some otehr custom visual that shows this. At least I know now that this is not really possible with Power BI.
Best regards, Jakob
Hi, @Anonymous ;
Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here.
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous ;
You could try to create a measure as follows:
sales%1 = CALCULATE(SUM([Sales EUR]),ALLEXCEPT('Table','Table'[Time Period],'Table'[Brand]))/CALCULATE(SUM([Sales EUR]),ALLEXCEPT('Table','Table'[Time Period]))
Or
sales%2 =
CALCULATE (
SUM ( [Sales EUR] ),
FILTER (
ALL ( 'Table' ),
[Time Period] = MAX ( [Time Period] )
&& [Brand] = MAX ( [Brand] )))
/ CALCULATE (
SUM ( [Sales EUR] ),
FILTER ( ALL ( 'Table' ), [Time Period] = MAX ( [Time Period] ) ))
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you very much for your help!
However, both measures shwo the same result if as I would just switch the "Stacked column chart" to the "100% stacked column chart". But this is not what I need unfortunately....
The bars should each keep theri original size (i.e. 66 or 63 and I still need to show this toatl value on top of each bar. Not just 100% on the top.)
Please see this example I created with another software:
Do you know if this is possible?
Best Regards,
Jakob
Hi @selimovd ,
thank you for the hint! Can you also guide me how I should customize this in the posted example. Obviously just switching sales data to percentage is not correct 🙂
Hey @Anonymous ,
the easiest way is if you format is as percentage and in the measure divide by 100. Then you get the right result as percentage.
There are workarounds, but they are all a pain.
Hey @selimovd ,
thank you, already prepared for pain 🙂
I cannot find the right measure formula. Any idea please?
Hey @Anonymous ,
just create a measure as the following and format it as percentage:
Sales EUR Pct = [Sales EUR] / 100
That should give you the result you want.
Best regards
Denis
Hey @selimovd ,
I already tried that, but this is not what i want:
each single bar is 100% and I want just to show the values within as percentages of the total (and these totals should remain, e.g. 66million in 2019 or 63million in 2020.)
I managed to show me the right numbers in a matrix when I formatted the values as show vales as % of row total (Please see below). But this option is not available in the chart.
So I guess what I am rellay looking for is a measure formula to calculate that numbers?
Thank you very much for your help!
Hey @Anonymous ,
if you want to display the values as percentage you could use the custom format strings to display accordingly:
Use custom format strings in Power BI Desktop - Power BI | Microsoft Docs
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 |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
138 | |
107 | |
104 | |
82 | |
63 |