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 !!