Filter visual table with a calculated value and keeping all filters applied

[ChrisTof]

Hi everyone,

I'm trying to filter my visual table using a calculated measure.

I have a visual table displaying the number of days, something like this :

Number of days
1170
804
511
355
346
288
274
268
12
24
126

When i use my slicers, the visual table displays what corresponds and all is good.

Now i've created a calculated measure to get the average number of days depending on selected slicers (here 379,81).

AverageDays = CALCULATE(AVERAGE(Table[NumberOfDays]))

I'm trying to show in my visual table only the values greater than this measure (average number of days), like this :

Number of days

1170

804

511

I can do it manually by going into "Filters" and entering "379,81" for my column.

But i want it to be automatic.

I've been searching for few days without finding anything.

Can someone help me please ?

[ChrisTof]

hi @v-tangjie-msft ,

I've just tried your solution and it works great !!!

Thanks a lot for your help

[v-tangjie-msft · Accepted solution]

Hi @ChrisTof ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create measures.

AverageDays = 
var a=SUMX(ALLSELECTED('Table'),'Table'[Number of days])
var b=COUNTROWS(FILTER(ALLSELECTED('Table'),'Table'[Number of days]<>BLANK()))
return a/b

Measure = IF(MAX('Table'[Number of days])>'Table'[AverageDays],1,0)

(3) Filter the visuals as shown in the following image and then the result is as follows.

Best Regards,

Neeko Tang

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

[ChrisTof]

hi,

I've got another question but i can create a new thread if you want.

I'm still using the visual table with your solution to filter.

I'm now using another column :

Number of days	Type
1170	Red
804	Red
511	Green
355	Orange
346	Orange
288	Green
274	Green
268	Red
12	Red
24	Green
126	Green

When i've filtered the visual, i get this :

Number of days	Type
511	Green
804	Red
1170	Red

Now, i want to let the users to be able to filter by type using slicer. I mean when the select 'Red', they should get this :

Number of days	Type
804	Red
1170	Red

But in fact they get this because when they use the slicer 'type' the measure 'AverageDays' is automatically updated. So the AverageDays was 380 and is now 987 and it displays only values greater than this.

Number of days	Type
1170	Red

I've tried using 'modify interactions' for slicer to set 'none' for the measure 'AverageDays' but still not working.

How can i do so the measure isn't updated when using the slicers ?

Thanks a lot again !