Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

DAX Select all except topN

Hello all   Is there a way to select all values from a table except the topN?      Sample data can be found here: sample data onedrive    I want to know the impact of the top 3 worse employees...
Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi Anonymous

     

    You may check below measure.

    Measure =
VAR a =
    SUMMARIZE (
        sampledata,
        sampledata[employeename],
        "AboveAverageAgent", AVERAGE ( sampledata[timetocomplete] )
    )
VAR b =
    ADDCOLUMNS ( a, "rank", RANKX ( a, [AboveAverageAgent] ) )
RETURN
    CALCULATE (
        SUM ( sampledata[completedintime] ) / DISTINCTCOUNT ( sampledata[orderid] ),
        FILTER ( b, [rank] > 3 )
    )

    Regards,
    Cherie

PattemManohar's avatar
PattemManohar
Icon for Community Champion rankCommunity Champion
7 years ago

Anonymous It will be great if you can provide us sample data and expected output..... You DON'T need to provide actual data...

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Added it :)