Need help averaging a nested DAX function

Question

Need is to find the top 6 of production "values" between operators("op"). Secondly to average them across each op. I need help with the DAX code.

I tried creating a separate table using topN to find top values. Then used AverageX, CurrentGroup and GroupBy. The error is that GroupBy statement must find the average across CurrentGroup, must refer to CurrentGroup or a constant.

Table = 
GROUPBY (
    GROUPBY (
        fat,
        'fat'[op],
               "topn", TOPN( 6, fat, fat[Value],DESC)
    ),
    'fat'[op],
    "mn",SUMX( CURRENTGROUP (), [topn] )
)

This is easier done in R:

  dt<-read.table("data2.csv", TRUE) 
  library(plyr)
  n=6 #or other
  df1<-ddply(dt,~name,summarise,mean=mean(tail(sort(val),n)))

:A sample of the input and expected output is linked:

Alexis Olson Alexis Olson · Accepted Answer · 2018-12-29T23:59:43

If I'm understanding correctly, you want to take the top 6 values for each op and average those to get a single row.

If that's correct, then I'd suggest something like this:

AvgTop6 = 
SUMMARIZECOLUMNS(
    fat[op],
    "Mean",
    CALCULATE(
        AVERAGE(fat[Value]),
        FILTER(fat, RANK.EQ(fat[Value], fat[Value]) <= 6)
    )
)

Need help averaging a nested DAX function

1 Answers