Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Bohumil_Uhrin
Helper II
Helper II

Averagex with slicer

Hello,

I have a dataset of runners and times of their arrivals to checkpoints.
runners data.PNG

 

I need to do a pivot table, which will show average time of arrival to the checkpoint:pivot.PNG

 

 

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

(here I saved my file)

 

Any help appreciated 🙂

1 ACCEPTED SOLUTION
v-cherch-msft
Microsoft Employee
Microsoft Employee

Hi @Bohumil_Uhrin

 

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")

2.png

 

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

1.png

 

Regards,

Cherie

 

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-cherch-msft
Microsoft Employee
Microsoft Employee

Hi @Bohumil_Uhrin

 

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")

2.png

 

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

1.png

 

Regards,

Cherie

 

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.