Dax Average with Distinct Filter

[Erik_Debono]

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"

ID	CycleTime	Result
1	10	N/A
1	10	Pass
1	10	Fail
1	10	N/A
2	34	N/A
2	34	Pass
2	34	Fail
2	34	Fail
2	34	Fail
3	13	Fail
3	13	N/A

Thank you

[Anonymous · Accepted solution]

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.

[Erik_Debono]

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]

@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])

[Erik_Debono]

@Anonymous perfect. I understand now. Thank you.

[Erik_Debono]

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]

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

[amitchandak]

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