Forum Discussion
Filter column chart by more slicers
Hello,
could you please help me with one column chart in Power BI Desktop?:
I have a simple data table:
Now I create a column chart (share of "count"):
Now I created one slicer using column "Y/N" from my data table and also another 3 slicers from my data table (slicer 1, slicer 2, slicer 3). So I have 4 slicers.
What I would like to do is, when I select "no" from slicer "Y/N", I will see the column chart without the brand Bosch. But the values must be unchanged. It shoul look like this:
I use this measure:
DIVIDE ( SUM ( Table1[count] ), CALCULATE ( SUM ( Table1[count] ), ALL ( Table1 ) ) )
It works (it gives me the correct results) well in the case that I donť use other slicers (slicer 1, slicer 2 and slicer 3). But I need to use them. When I start to use other slicers, the chart shows not correct results.
How should I change the measure? Or any other solution?
Thank you for your help
6 Replies
- HotChilli
Community Champion
"When I start to use other slicers, the chart shows not correct results" - this is the main problem, isn't it?
Can you explain what's happening and also please link the pbix so we can have a look?
I canť upload the PBIX file, so I will try to exlain it in more detail.
This is my Data table:
Last column "RoB yes / no" is calculated: RoB yes / no = if('QTY detail'[Brand] = "RoB", "yes", "no")
I created one measure:
share % = divide(sum('QTY detail'[Count]), CALCULATE(sum('QTY detail'[Count]), all('QTY detail'[Brand])), 0)And use this measure to create one card and one bar chart:As you can see the Bosch share is 8,7% in all these visuals.
I also created several Slicers (column from data table = slicer):
My goal is just to hide the results (the bar) "RoB" in the bar chart. I want to create a new slicer (or maybe use of my existing slicers, I don' know) to hide the bar "RoB" in the bar chart.
This new slicers should contain:
"Yes, I want to display RoB in chart" and "No, I don't want to display RoB in chart".
But using this new slicer, the results in the charts must not be recalculated.
So if the Bosch share is 8,7%, without RoB bar the Bosch share must be also 8,7%.
When I use another slicer "MDA/SDA" and select "MDA" from this slicer, the Bosch share is 5,9%. When I use the new slicer to hide the RoB bar, the Bosch share must be still 5,9%.
Is it clear now?
Thank you very much
- HotChilli
Good explanation.
I think you want to remove all the filters on the denominator of the measure share %.
Can you test that at your side? Just create a copy of the measure and edit the
CALCULATE(sum('QTY detail'[Count]), all('QTY detail'[Brand]) to remove filters
I donť fully understand, what should I do. When I create this new mesure:
Measure = calculate(sum('QTY detail'[Count]), ALL('QTY detail'))it doesnť work. I don't get the share of brands.
- HotChilli
That looks right for the denominator. You'll still need the divide(sum('QTY detail'[Count]) part
No, this doesn't work.
I use this measure:
share % = divide(sum('QTY detail'[Count]), CALCULATE(sum('QTY detail'[Count]), all('QTY detail'[Brand]))).It seems that the measure is exactly as you propose.And how to continue now? How to create a slicer that hide the RoB from the bar chart without changing the results?I tryied to create a slicer from column "RoB yes / no" from my data table.When I select "no" in this slicer (no = all brands without Bosch) I get this bar chart:
I don't see RoB bar (that's super) but the Bosch share is not correct. I still should see 8,7%. As I can see without using this slicer.
