Forum Discussion
Slicers & Data Display
Hi SuryaDave_Syd ,
Looking at the model you have you need to create a disconnect table that will allow you to do the filtering of your data and then a measure to make it to work.
Before giving the step by step, let me try and explain what is happening, when you filter to a specific city you data will only show that city value, because since everything is in the same table you are not abble to tell the data to show something that is not consider.
If you filter on City_A this is what you get
So the calculations cannot present anything else since when you search for State Level that value is not present on the current filtering selection.
If you had a table do the filter you can then change the final result because you can influence how the city will be filtered out.
- Create a new table with the BU
- Add the following measure to your model
Average Value = IF( SELECTEDVALUE(Sheet1[Business_Unit]) = "STATE_LEVEL" || SELECTEDVALUE(Sheet1[Business_Unit]) = SELECTEDVALUE(BU[Business_Unit]), AVERAGE(Sheet1[Values]) )- Use the new table on your slicers and the measure on the values
Has you can see now chart 2 is equal to chart 1 where you forced the values.
PBIX file attach.
Hi Miguel, this is perfect & doing exactly what I was aiming to achieve. Also appreciate explanation & logic so that I learn to fish.
You are champion !!! Thanks very much & more power to you !!
Thanks for your response Miguel. However, that is not what my problem is. My slicers are going to be single select and it will be synced across pages like I mentioned in my post.
The problem is " when slicers are synced it will obviously filter the data / chart. But what I want is, chart should display whatever the selection done in single select slicer and in addition to that it should display other item as well for comparison. ( as business requirment is slicer has to be single select - need to come up with other solution to acheive it ) please refer to my PBI file where I have given detailed explanation of my problem.
Hi SuryaDave_Syd ,
Looking at the model you have you need to create a disconnect table that will allow you to do the filtering of your data and then a measure to make it to work.
Before giving the step by step, let me try and explain what is happening, when you filter to a specific city you data will only show that city value, because since everything is in the same table you are not abble to tell the data to show something that is not consider.
If you filter on City_A this is what you get
So the calculations cannot present anything else since when you search for State Level that value is not present on the current filtering selection.
If you had a table do the filter you can then change the final result because you can influence how the city will be filtered out.
- Create a new table with the BU
- Add the following measure to your model
Average Value = IF(
SELECTEDVALUE(Sheet1[Business_Unit]) = "STATE_LEVEL" || SELECTEDVALUE(Sheet1[Business_Unit]) = SELECTEDVALUE(BU[Business_Unit]),
AVERAGE(Sheet1[Values])
)- Use the new table on your slicers and the measure on the values
Has you can see now chart 2 is equal to chart 1 where you forced the values.
PBIX file attach.
Hi Miguel, this is perfect & doing exactly what I was aiming to achieve. Also appreciate explanation & logic so that I learn to fish.
You are champion !!! Thanks very much & more power to you !!