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.
Hello,
I'm trying to find the average of two columns that are identified using two columns.
The following is a dummy example of the problem I'm having:
So basically if the columns have the same ship and tour number,
I want to find the average from the timestamp column from where the id is 5 and 12 for the entire data.
Also if there does not exist an either id 5 or 12 I don't want it taken with in the calculation.
Thanks,
Jonas
Solved! Go to Solution.
Hi @Anonymous ,
Please refer to below measures:
TimeStamp start = IF ( SELECTEDVALUE ( Test_7[Id] ) = 12, CALCULATE ( MAX ( Test_7[Timestamp] ), FILTER ( ALLEXCEPT ( Test_7, Test_7[Ship], Test_7[Tour] ), Test_7[Id] = 5 ) ), BLANK () ) diff = DATEDIFF([TimeStamp start],SELECTEDVALUE(Test_7[Timestamp]),HOUR) average = AVERAGEX(Test_7,[diff])
Best regards,
Yuliana Gu
Hi @Anonymous ,
Please refer to below measures:
TimeStamp start = IF ( SELECTEDVALUE ( Test_7[Id] ) = 12, CALCULATE ( MAX ( Test_7[Timestamp] ), FILTER ( ALLEXCEPT ( Test_7, Test_7[Ship], Test_7[Tour] ), Test_7[Id] = 5 ) ), BLANK () ) diff = DATEDIFF([TimeStamp start],SELECTEDVALUE(Test_7[Timestamp]),HOUR) average = AVERAGEX(Test_7,[diff])
Best regards,
Yuliana Gu
Great, thank you
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |