Forum Discussion

Sid777's avatar
Sid777
Frequent Visitor
2 years ago

dax function

Good orders = CALCULATE([Order Count],FILTER(table_orders,[Actual order value]>AVERAGE(table_orders[Bill_amt])))
 
where [Actual order value] =
Actual order value = CALCULATE(SUM(table_orders[Bill_amt]),table_orders[DeliveryStatus]<>"Cancelled").

Here can some tell me that the measure  [Actual order value] used in Good orders, is an aggregrated value?

If yes, then the Good orders should ideally count all row because aggregated values is always bigger then the average.
Need Help

5 Replies

  • Sid777's avatar
    Sid777
    Frequent Visitor
    2 years ago

    Sir, these are the three measures
    1)Actual     order value = CALCULATE(SUM(table_orders[Bill_amt]),table_orders[DeliveryStatus]<>"Cancelled")
    2)Average amount of order = AVERAGE(table_orders[Bill_amt])
    3)Good orders = CALCULATE([Order Count],FILTER(table_orders,[Actual order value]>AVERAGE(table_orders[Bill_amt])))
     
    Sir, the result for  Good orders is coming out to be 6
    My doubt is that in Good orders = CALCULATE([Order Count],FILTER(table_orders,[Actual order value]>AVERAGE(table_orders[Bill_amt]))), isn't [Actual order value] an aggregated value, which is same for all rows.
     
    Regards

     

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      2 years ago

      It is not an aggregated value because the table you have provided is table_ordes in the Good Orders measure.  So the masure is being evaluated for each row.  If you want to receive any further help, share some data to work with, explain the question and show the expected result. 

      • Sid777's avatar
        Sid777
        Frequent Visitor
        2 years ago

        Sir, in the similar context when I use the measure [Average amount of order] inside the FILTER function 

        Good order 1 = CALCULATE([Order Count],FILTER(table_orders,[Actual order value]>[Average amount of order])), you can see in the screen shot the result is coming "blank". Could you please advice why the result is coming blank .
         
        Good orders = CALCULATE([Order Count],FILTER(table_orders,[Actual order value]>AVERAGE(table_orders[Bill_amt])))
         
        Is there any difference in the working of Good orders and Good orders1