Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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])
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])
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.
Sure, it is basically 2 steps:
var _table = DISTINCT(
SELECTCOLUMNS(
FILTER('Cycle Time', [Result]<>"N/A"),
"ID", [ID],
"CycleTime", [CycleTime]
)
)
return AVERAGEX(_table, [CycleTime])
@Anonymous perfect. I understand now. Thank you.
Hi @Anonymous
Thank you for that. I tried your formula and i'm getting underline errors for the following:
The overall error is "Function SELECTCOLUMNS expects a column name as argument number 4"
Hi @Erik_Debono , @Anonymous ,
I built a table using copy and paste from your numbers, and it works for me.
Nathaniel
Proud to be a Super User!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |