cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bohumil_Uhrin New Member
New Member

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 Smiley Happy

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Averagex with slicer

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.
1 REPLY 1
Community Support Team
Community Support Team

Re: Averagex with slicer

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.