get average of subtotal and wet with respect to distinct purchaseorderid and product category in DAX-CodePudding

i don't know where to start, pretty new in power bi or Dax here is sample data

  PurchaseOrderId    SubTotal    Wet         ProductCategory   
 ------------------ ----------- ----------- ------------------ 
  1021               804.9767    233.4432    Wine              
 ------------------ ----------- ----------- ------------------ 
  1022               228.4651    66.2548     Beer              
 ------------------ ----------- ----------- ------------------ 
  1022               228.4651    66.2548     RTD               
 ------------------ ----------- ----------- ------------------ 
  1022               228.4651    66.2548     Wine              
 ------------------ ----------- ----------- ------------------ 
  1023               2791.2558   809.4641    Wine              
 ------------------ ----------- ----------- ------------------ 
  1023               2791.2558   809.4641    Beer              
 ------------------ ----------- ----------- ------------------ 
  1023               2791.2558   809.4641    Non-alcoholic     
 ------------------ ----------- ----------- ------------------ 
  1023               2791.2558   809.4641    RTD               
 ------------------ ----------- ----------- ------------------ 
  1024               396         114.84      Wine              
 ------------------ ----------- ----------- ------------------ 
  1025               374.2325    108.5274    Wine              
 ------------------ ----------- ----------- ------------------ 
  1026               864.093     250.5869    Wine              
 ------------------ ----------- ----------- ------------------ 
  1027               127.9069    37.093      Wine

what i want is i want average order value i.e (Subtotal Wet)/Count(Distinct PurchaseOrderId). For this table Sum of distinct Subtotal is 5586.93 and Sum of distinct wet is 1620.20, total number of distinct purchaseorderid i.e number of orders is 7 so my average order value is (5586.93 1620.20)/7 = 1029.59, i also want average order value by category i am doing everything in DAX so how this can be achieved? Thanks in advance

CodePudding user response：

Use this as a new Measure

Avg Subtotal = 
AVERAGEX(
    SUMMARIZE(
        'Table',
        'Table'[PurchaseOrderId],
        'Table'[ProductCategory],
        'Table'[SubTotal]
    ),
    'Table'[SubTotal]
)

CodePudding user response：

You need to use this measure: I called my table (Test00) You can use yours in the same way.

AverageOfSubTotal = 
VAR TblSummary = ADDCOLUMNS(
    SUMMARIZE (Test00, Test00[PurchaseOrderId], Test00[ProductCategory]),
    "Total_SubTotal",CALCULATE(SUM(Test00[SubTotal]))
)
RETURN
    AVERAGEX(TblSummary,[Total_SubTotal])

If we test it on a table visual:

Now let's test the visual table report performance on DAX Studio:

The results are quite good!! 5 ms query time (4ms FE, 1 ms SE). 4 SE(Storage Engine) queries.