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 get the table with visual lever filter. For example, you may create a rank measure and use it in visual level filter. 

AboveAverageAgent =
CALCULATE (
    SUM ( Table3[timetocomplete] ) / COUNT ( Table3[employeename] ),
    ALLEXCEPT ( Table3, Table3[employeename] )
)
Rank = RANKX(ALL(table3),[AboveAverageAgent],,DESC,Dense)

 

If it is not your case, please share some data sample and expected output. 

 

Regards,

Cherie

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    hi v-cherch-msft,

     

    Thanks for your answer, but it doesnt quiet fit. It works, but not ideal as i still have to filter "rank" to a top 3. 

    This file is going to be used for different teams and its not ideal for the end users to manually change the filter in case there are no double rank 1 and 2's.

     

    Let me give some more information, i see that my original post is lacking that. 

     

    I want to calculate the SLA with the following calculation SLA = (SUM(completedintime)/DISTINCTCOUNT(orderid))*100 

     

    The expected result is the SLA of all employees BUT the top 3 based on worse timetocomplete. To see the  impact on the SLA if the Top (or bottom) 3 

     

    In the samplefile the SLA would be 62 (%)

    without the top 3 (C,D&G) it would be 67 (%)

    • 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!