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 have a dataset of runners and times of their arrivals to checkpoints.
I need to do a pivot table, which will show average time of arrival to the checkpoint:
To create a measure like this, I used the following formula:
average2:=averagex(all('sample'[Name];'sample'[Time]);'sample'[max])
Where "max" is simply:
max:=max('sample'[Time])
Now the problem is, when I used a slicer on the runner name, it does not affect the calculation. Its prorably because of using "ALL" function, but I couldnt create a formula without it.
I also tried something like this, but didnt work like I wanted.. it just returned actual times, not averages:
average1:=AVERAGEX(VALUES('sample'[Checkpoint]);'sample'[max])
Any help appreciated 🙂
Solved! Go to Solution.
If you want show the average by runner name, you may create a measure as below.
average3 = FORMAT(averagex(ALL('sample'[Time],'Sample'[Checkpoint]),'sample'[max]),"HH:MM")
If you want to use slicer to change the average value for the table, you may use DISTINCT('Sample'[Name]) to create a slicer table first. Then you may get the table with below measure:
average2 = FORMAT(averagex(all('sample'[Name],'sample'[Time]),'sample'[max]),"HH:MM")
average4 = IF(HASONEVALUE(Slicer[Name]),[average3],[average2])
Regards,
Cherie
If you want show the average by runner name, you may create a measure as below.
average3 = FORMAT(averagex(ALL('sample'[Time],'Sample'[Checkpoint]),'sample'[max]),"HH:MM")
If you want to use slicer to change the average value for the table, you may use DISTINCT('Sample'[Name]) to create a slicer table first. Then you may get the table with below measure:
average2 = FORMAT(averagex(all('sample'[Name],'sample'[Time]),'sample'[max]),"HH:MM")
average4 = IF(HASONEVALUE(Slicer[Name]),[average3],[average2])
Regards,
Cherie
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 |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |