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

v-cherch-msft's avatar
v-cherch-msft
Icon for Microsoft Employee rankMicrosoft Employee
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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi v-cherch-msft,

 

Perfect! does the job just how I want it. I didn't know I could use variables in a measure, a new world just opened up for me haha. Going to have some fun with this, thanks!