Forum Discussion

Erik_Debono's avatar
Erik_Debono
Advocate II
6 years ago
Solved

Dax Average with Distinct Filter

Hi All,

 

I'm trying to filter a table (have provided a mock one below) where I filter by Distinct and a result. As you can see, the Cycle time will be the same for every ID but I only want to Average each ID once. If I Average the entire table below, the result is 21.45. If I Average only one instance of each ID, the result is 19. 19 is the value I need.

 

My Distinct filter doesn't seem to be doing anything. I have tried multiple variations of my below query but this is the one that makes the most sense to me but i just can;t work out why it's not giving me what i want. 

 

Cycle Time Average:=CALCULATE(AVERAGE('Cycle Time'[CycleTime]),DISTINCT('Cycle Time'[ID]),'Cycle Time'[Result]<>"N/A")

 

Table is Called "Cycle Time"

IDCycleTimeResult
110N/A
110Pass
110Fail
110N/A
234N/A
234Pass
234Fail
234Fail
234Fail
313Fail
313N/A

 

Thank you

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Erik_Debono -

    Try the following:

    Cycle Time Average = 
var _table = DISTINCT(
    SELECTCOLUMNS(
        FILTER('Cycle Time', [Result]<>"N/A"), 
        "ID", [ID],
        "CycleTime", [CycleTime]
    )
)
return AVERAGEX(_table, [CycleTime])

     

    I hope this helps. If it does, please Mark as a solution.
    I also appreciate Kudos.
    Nathan Peterson

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Erik_Debono -

    Try the following:

    Cycle Time Average = 
var _table = DISTINCT(
    SELECTCOLUMNS(
        FILTER('Cycle Time', [Result]<>"N/A"), 
        "ID", [ID],
        "CycleTime", [CycleTime]
    )
)
return AVERAGEX(_table, [CycleTime])

     

    I hope this helps. If it does, please Mark as a solution.
    I also appreciate Kudos.
    Nathan Peterson
    • Erik_Debono's avatar
      Erik_Debono
      Advocate II
      6 years ago

      Hi Anonymous 

       

      Thank you for that. I tried your formula and i'm getting underline errors for the following:

      1. Name 2 [ID] on line 5
      2. Name 3 [CycleTime] on line 6 and
      3. Expression [CycleTime] on the last line.

      The overall error is "Function SELECTCOLUMNS expects a column name as argument number 4"

      • Nathaniel_C's avatar
        Nathaniel_C
        Community Champion
        6 years ago

        Hi Erik_Debono , Anonymous ,
        I built a table using copy and paste from your numbers, and it works for me.
        Nathaniel

         

    • Erik_Debono's avatar
      Erik_Debono
      Advocate II
      6 years ago

      Hi Anonymous Nathaniel_C 

       

      Error was self inflicted, your solution worked great, thank you for that. Have marked as Solved. Are you able to explain what's happening here? i'm not following how it works. 

       

       

      amitchandak  Thanks for your solution. I didn't get around to checking if it would work in this case. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Erik_Debono  - 

        Sure, it is basically 2 steps:

        1. Create a table of the distinct values:
          1. FILTER limits table to eliminate N/A
          2. SELECTCOLUMNS chooses the 2 columns we need to run the distinct on.
          3. DISTINCT returns the distinct values
            var _table = DISTINCT(
    SELECTCOLUMNS(
        FILTER('Cycle Time', [Result]<>"N/A"), 
        "ID", [ID],
        "CycleTime", [CycleTime]
    )
)
        2. Get the average of the CycleTimes from the distinct table.
          1. Use AVERAGEX to work with Table Variables.
        return AVERAGEX(_table, [CycleTime])

         

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    This seems like very similar to sum of Averages problem

    Refer

    https://community.powerbi.com/t5/Desktop/SUM-of-AVERAGE/td-p/197013

     

    In the calculation, you can replace Average with Max or Average. replace sum with Average  

     

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
    Thanks.

    My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601