Stacked bar chart with two values - add a tooltip to show percentage as well as whole number?

[Anonymous]

Hi all,

I've been getting stuck with the following, would appreciate some help as struggling to find what I'm after.

Working with a stacked bar chart with whole numbers running across the x axis and specific labels up the Y axis. There is a legend splitting the bars into two specifics (let's say dark and light for this example) within that category as per the image below. 

The split was achieved by adding two data points (dark and light) to the values well as there are some rows where they have occurences of just dark, some with just light and some with both. 

I want to be able to hover over and get a tooltip that tells me not only the whole number in that category, but also the percentage e.g. if the first bar was 100 in total, 70 dark and 30 light, I would then want the percentage tooltip to show 70% and 30% as well.

So for the first bar I would want to see:

Category: ____________
Dark: 70

Dark Percent: 70% 

When hovering over the dark and 

Category: ____________
Light: 30

Light Percent: 30% 

When hovering over the light and then the same down the entire set of categories.

I created a measure to add up both the dark and light and then divide by the whole count to arrive at a percentage but all that does is give me what percentage the whole bar is as opposed to the split. I think having the two values in the well is causing the issue so is there any other workaround you can think of?

Thank you!

[BI_Jo]

Hi,

Try creating this measure:

Percentage = DIVIDE ( SUM ( 'Table'[Count] ), CALCULATE ( SUM ( 'Table'[Count] ), ALL ( 'Table'[Colour] ) ), 0)

In my example the Count column in the table is in the Value section of the Stacked Bar Chart and the Percentage Measure is added in the tooltips section.

Hope that works for you. 
Jo

[Anonymous]

Hi @BI_Jo - can you clarify what ALL ( 'Table'[Colour] ) is referencing?

Apologies if my wording was not clear, but my colouring is as a result of the 2 values in the table i.e. 

Column 1 = Light

Column 2 = Dark

So I think I'm following the bit where you mention the count
( SUM ( 'Table'[Count] ), CALCULATE ( SUM ( 'Table'[Count] ) 
as this is where I am putting in my two light and dark values

just not following the last part - the reason I have had to use two values is because some categories will have just light, some will have just dark and some will have both.

[BI_Jo · Accepted solution]

Hi,

If that's how you have your table I would recommend unpivoting the light and dark columns into a single column.  So the table would have 2 rows for each category, one with the number of light and the other with the number of dark.  I called this column 'colour' in my table.

The measure should then work for you.

Jo

[Anonymous]

Thank you very much! I think that's resolved it!

[BI_Jo]

Great, I would recommend always unpivoting tables in powerBI, better for the data model, measures and visualisations.

Jo