Forum Discussion

ChrisTof's avatar
ChrisTof
Frequent Visitor
3 years ago
Solved

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

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 ?

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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. 

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      ChrisTof
      Frequent Visitor
      3 years ago

      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 daysType
      1170Red
      804Red
      511Green
      355Orange
      346Orange
      288Green
      274Green
      268Red
        
      12Red
      24Green
      126Green

       

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

      Number of daysType
      511Green
      804Red
      1170Red

       

      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 daysType
      804Red
      1170Red

       

      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 daysType
      1170Red

       

      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 !

  • ChrisTof's avatar
    ChrisTof
    Frequent Visitor
    3 years ago

    hi Anonymous ,

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

    Thanks a lot for your help