Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Show percentages in stacked column chart and filter by slicer

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.

 

NittyGritty_0-1625579383614.png

Dummy Numbers:

 

Time PeriodSales ChannelDriveBrandSales EUR
2019onlinewith batteryCompetitor A5 €
2019onlinewo batteryCompetitor A3 €
2019offlinewith batteryCompetitor A7 €
2019offlinewo batteryCompetitor A4 €
2019onlinewith batteryCompetitor B7 €
2019onlinewo batteryCompetitor B8 €
2019offlinewith batteryCompetitor B9 €
2019offlinewo batteryCompetitor B2 €
2019onlinewith batteryCompetitor C3 €
2019onlinewo batteryCompetitor C6 €
2019offlinewith batteryCompetitor C4 €
2019offlinewo batteryCompetitor C8 €
2020onlinewith batteryCompetitor A3 €
2020onlinewo batteryCompetitor A6 €
2020offlinewith batteryCompetitor A5 €
2020offlinewo batteryCompetitor A9 €
2020onlinewith batteryCompetitor B1 €
2020onlinewo batteryCompetitor B7 €
2020offlinewith batteryCompetitor B2 €
2020offlinewo batteryCompetitor B4 €
2020onlinewith batteryCompetitor C6 €
2020onlinewo batteryCompetitor C8 €
2020offlinewith batteryCompetitor C8 €
2020offlinewo batteryCompetitor C4 €

 

 

Thank you very much for any help!

14 REPLIES 14
v-yalanwu-msft
Community Support
Community Support

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

vyalanwumsft_0-1625737327447.png

2.Adjust the display

vyalanwumsft_1-1625737373907.png  vyalanwumsft_2-1625737406207.png  vyalanwumsft_3-1625737597041.png

3.The final output is shown below:

vyalanwumsft_4-1625737749623.png

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. 

Anonymous
Not applicable

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?

NittyGritty_0-1625749132825.png

 

Hi, @Anonymous ;

This is difficult to achieve, or you try to use tooltip, such as:

vyalanwumsft_0-1625823328425.pngvyalanwumsft_1-1625823422511.png

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.

 

Anonymous
Not applicable

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.

v-yalanwu-msft
Community Support
Community Support

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:

vyalanwumsft_0-1625724128318.png

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.

Anonymous
Not applicable

Hi @v-yalanwu-msft 

 

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:

NittyGritty_0-1625734833894.png

Do you know if this is possible?

 

Best Regards,
Jakob

Anonymous
Not applicable

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 🙂

NittyGritty_0-1625585117283.png

 

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.

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 
Anonymous
Not applicable

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

Anonymous
Not applicable

Hey @selimovd ,

 

I already tried that, but this is not what i want:

NittyGritty_0-1625671912797.png

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!

Anonymous
Not applicable

Hey @selimovd 

NittyGritty_1-1625672350494.png

 

 

selimovd
Super User
Super User

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

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.