Forum Discussion

Abevann's avatar
Abevann
Icon for Helper III rankHelper III
5 years ago

Filter the lowest value over a threshold

Guys, 

 

I guess is a very simple question, the biggest problem is how to ask the question I guess. I have a table with articles and price; nevertheless the customer for whatever reason, have values that make no sense; this table for instance:

ItemCost
Cereal250
Cereal220
Cereal320
Cereal240
Cereal300
Cereal20

I have to get the lowest price (in this table 20), but in this case this one seems to be wrong. So I would like to stablish a theshold, for instance 20%. That means that if the cost value is less than average minus 20% this should be a mistake and I should take the lowest price over the threshold (in this instance 220 since the average minus 20% is 180). So I should not take into account the wrong value; how to do so?

 

Thanks a lot, regards,

Abelardo

Need Help

3 Replies

    • Abevann's avatar
      Abevann
      Icon for Helper III rankHelper III
      5 years ago

      Hey Sevenhills,

       

      Thanks for your quick reply. Unfortunately, my description is not so good; nevertheless taking a look of what you sent me, some ideas came into my head, and got the solution. 

       

      This DAX give me what I am looking for, I added it for anyone that should find it helpful.

       

      Minimum price = CALCULATE(MIN(INV1[Price]),
      FILTER(INV1, INV1[Price] > (AVERAGE(INV1[Price]) * 0.80))
      )
       
      Thanks, regards,
      Abelardo
  • sevenhills's avatar
    sevenhills
    Icon for Super User rankSuper User
    5 years ago

    Glad you are able to solve on your own and my notes helped you in attaining the result.

     

    Appreciated for posting the solution for others.

    Regards