100% stacked bar chart - show relative percentage when values are selected in a slicer

[AnthNC]

Hi everyone,

Here's the link to the pbix :

https://drive.google.com/file/d/157QN-4qO7g-luAnnAbpEMrBG_oJNNy3Q/view?usp=sharing 

Project

Several people were asked in what types of grocery store do they shop. So i've unpivoted the data like so where 1 id (1 person) can correspond to several rows (types of stores they go to) :

Question 1

In pbi desktop, i'm showing the data in a 100% stacked bar chart with years on the x axis and the 0-100% y axis being turned off so it looks like that :

There are 2 slicers : years and types of stores.

When i select a type of stores (supermarkets for example), the results are displayed like that :

Is it possible to show the data like on a regular stacked bar chart when one or several values are selected in the types of store slicer, like so :

Question 2

Either on a 100% or regular stacked bar chart, is it possible to show the total of distinct id instead of the total labels.

So instead of showing the total labels like this :

I'd like to display the total of distinct id per year, like this :

Do you guys know a way to do that ?

Thank you for your help !

[Ritaf1983 · Accepted solution]

Hi @AnthNC 

Since a 100% stacked bar chart is a chart where the total height of the bar is always 100%, selecting slicers will not change the height of the columns. In other words, you cannot display different heights in this type of chart.

As for displaying absolute values in addition to percentages, this is possible - see the attached image .

As for the total - this is not possible because by the definition of the chart, each column is 100%, so there are no options for manipulation.

The updated pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[dash_bro_board]

Use the ALL() function to ignore what you're filtering and create a measure that evaluates the relative percentage. Put this measure into a normal (not 100%) stacked column chart and it will work as you described.

Leads_Stacked_% = [Leads] / CALCULATE( [Leads], ALL( LeadSourceMap[Leadsource] ) )

Then when I filter by Leadsource it keeps the relative percentage:

Before Filter:

After Filter:

[Anonymous · Accepted solution]

Hi,@AnthNC Hello,@Ritaf1983 ,

thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below

Thank you very much for the complete .pbix file and for detailing your problem

Let me answer these questions for you
Question 1
Unfortunately, when using a 100% stacked column chart, you want it to show similar results to a normal stacked column chart:

When the slicer selects a piece of data, each column of results displays a different height based on its percentage

But this goes against the definition of a 100% stacked column chart:

Display the columns as a whole, with line segments indicating the proportion of subcategories in each whole. It visually shows the overall percentage of each subcategory, which is always 100% for different series, which is why it was named “100%”.

These is the relevant document, I hope it can help you:
URL:
100% Stacked Column Chart: Visualizing Proportions | Bold BI
Question 2
If you want to display the total number of different IDs instead of the total number of tags, you can do so by creating a calculate column
like this:

C_result = 
VAR years_num=[years]
RETURN
 CALCULATE(DISTINCTCOUNT('Feuil1'[id]),FILTER(ALL('Feuil1'),Feuil1[years]=years_num))

put the calculate column into the visual

Unfortunately, this cannot be solved with measure

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AnthNC]

Thank you for your answers !

Question 1 - about column heights

I understand that a 100% stacked bar chart will always show the same column height.

In that case, isn't there a way to "trick" a regular stacked bar chart to show same column heights per year if there is no value selected in the slicer ?

[Anonymous]

Hi,@AnthNC 
Thank you for your replay.
Unfortunately, I don't think it's feasible, one of the features of regular stacked bar charts is that they show different heights depending on the value, and I don't think it's necessary to trick the bar charts so that they show inconsistent results but consistent heights

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ritaf1983 · Accepted solution]

Hi @AnthNC 

Since a 100% stacked bar chart is a chart where the total height of the bar is always 100%, selecting slicers will not change the height of the columns. In other words, you cannot display different heights in this type of chart.

As for displaying absolute values in addition to percentages, this is possible - see the attached image .

As for the total - this is not possible because by the definition of the chart, each column is 100%, so there are no options for manipulation.

The updated pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly