Hide some part of the bar with a selection

[Fh3r1008]

Hi all, 

Please I need your help with this issue.

I have a stacked bar chart and a little table called "opcion". I need to hide "some part" of the bar when I select some option of the table:

I mean, for example, if I select (in the little table) the word "Recibo"... the chart should be change and only show the blue part of the bars, if I select "Embarque" the chart should only show the gray part of the bars and if I select "Embarque" the orange part of the bars.

This is the table that feeds the chart:

Thanks in advance and I wait for your answer

[Ritaf1983 · Accepted solution]

Hi @Fh3r1008 
To achieve a wanted interaction you can apply these steps.
1. From PQ you need to unpivot the table :

2. Close and apply

3 There is 2 scenarios 
3.1 if you want the line of personas to stay when you click on table like in the attached picture :

Create 2 measures :
1.

Sum_Exclude_personas = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Category]<>"Personas"))

2. 

Value_personas = CALCULATE(sum('Table'[Value]),FILTER(ALL('Table'[Category]),'Table'[Category]="Personas"))

3. Create the graph, note that categories should be on the Legend

4 Create a table, and filter out the personas.

5. Format the interactions with the graph :

Result:

After table selection 

If you want the line be hided too , apply all the same steps except of the measure for personas.
It should be :

Value_personas_2 = CALCULATE(sum('Table'[Value]),FILTER('Table','Table'[Category]="Personas"))

The result will be :

A more detailed guide about unpivot here :
https://www.youtube.com/watch?v=tAIqopmj9RA

And about editing interactions here :
https://www.youtube.com/watch?v=5MteQv_h6ck

The pbix with the solutions is attached

If my answer was helpful please give me a Kudos and accept as a Solution.

[Fh3r1008]

Hi @Ritaf1983 

Thx a lot! Let me check it 

[Fh3r1008 · Accepted solution]

Hi @Ritaf1983 ,

It is great! Thx a lot!!

[Ritaf1983]

Happy to help 🙂

[Fh3r1008]

Hi all - @Ritaf1983,

I have another situation with this same case. We need to do a change and maybe you can help us.

I used the option 1 of your solution (keep show the line) but now we need this:

When we apply some filter, for example "Recibo", we need to show the "Recibo" bar (this is ok) but also we need to have a change in the line, I mean, the line should show only the value of persons for Recibo process.

Let me explain more, this is my table:

I mean, the 9 result is the sum of 3 different numbers. Red rectangle has the formula for calculate the persons for Recibo process, orangle rectangle for calculate persons for Reempaque process and green rectangle for Embarque process.

So, the change that we need is, for example, if I select the filter for Recibo... the bar now changes correctly but the line needs to change too with the value ONLY of the persons for recibo process (calculate with red rectangle formula).

At the row with red arrow we see the example... with that data, persons for Recibo is 1, for Reempaque 7 and for Embarque 1... sum 9.

Thanks in advance and I'll wait for your answer.

[Ritaf1983]

Hi @Fh3r1008 
I didn’t quite understand the logic of what you're trying to do, but Power BI doesn't recognize your formulas. If each component has a different number of personas, you need to load into Power BI 3 columns with the number of personas for each category separately. Then, during the unpivot stage, you’ll have both the categories and their corresponding number of personas in each row. This logic is essential when working with the tool to provide you with the flexibility and the ability to slice and filter the data correctly.

If my answer was helpful please give me a Kudos and accept as a Solution.

[Ritaf1983 · Accepted solution]

Hi @Fh3r1008 
To achieve a wanted interaction you can apply these steps.
1. From PQ you need to unpivot the table :

2. Close and apply

3 There is 2 scenarios 
3.1 if you want the line of personas to stay when you click on table like in the attached picture :

Create 2 measures :
1.

Sum_Exclude_personas = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Category]<>"Personas"))

2. 

Value_personas = CALCULATE(sum('Table'[Value]),FILTER(ALL('Table'[Category]),'Table'[Category]="Personas"))

3. Create the graph, note that categories should be on the Legend

4 Create a table, and filter out the personas.

5. Format the interactions with the graph :

Result:

After table selection 

If you want the line be hided too , apply all the same steps except of the measure for personas.
It should be :

Value_personas_2 = CALCULATE(sum('Table'[Value]),FILTER('Table','Table'[Category]="Personas"))

The result will be :

A more detailed guide about unpivot here :
https://www.youtube.com/watch?v=tAIqopmj9RA

And about editing interactions here :
https://www.youtube.com/watch?v=5MteQv_h6ck

The pbix with the solutions is attached

If my answer was helpful please give me a Kudos and accept as a Solution.