Forum Discussion

guilrosr's avatar
guilrosr
New Member
2 years ago

CALCULATE with FILTER not working

Hi all, 

I'm trying to add a FILTER to my CALCULATE formula, but it does not return any values.

The filter bases its parameters on the result of the formula: 

 

Overall Average Price = CALCULATE( [Average Retail Price], ALL( 'Product Categories Lookup'))

 

When running this formula by itself, it gives the expected result: $714.44

 


When testing the formula with the result as a "fixed number" in the calculate option, it works, but when using the Overall Average Price formula, there are no results:

   

 

 


Any idea why this is happening?

Thank you

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    guilrosr , Change filter like in non working case

    Filter(values('Product Lookup'[Category]), max('Product Lookup'[Product Price] > [Overall Average Price]))

     

    if Product Price is measure remove max

    • guilrosr's avatar
      guilrosr
      New Member
      2 years ago

      It still returns no values.
      My question is why, when adding the value itself (714.44 - which is the result of the formula Overall Average Price ), the filter works, but not with the formula.

       

      • rkaur's avatar
        rkaur
        New Member
        2 years ago

        Modify your Overall Average Price  to remove all filters from "Product Lookup" table instead of "Product Categories Lookup"

         

        Overall Average Price =
        CALCULATE(
            [Average Retail Price],
            ALL(
                'Product Lookup'
            )
        )
  • Arul's avatar
    Arul
    Icon for Super User rankSuper User
    2 years ago

    guilrosr ,
    Create a variable for average price and use that in calculate filter instead of referring the mesure directly, 

    High Ticket_Orders = 
VAR _Overallaverageprice = AVERAGE('Product'[List Price])
VAR _result = CALCULATE(
    [Total_Order],
    FILTER('Product',
    'Product'[List Price]>_Overallaverageprice))
RETURN _result