Column topn average

[Cris22]

Dear all,I would be grateful if you could help me with the following question:

I have a table (Data) with speeds for different drivers on different laps of a track and for different years and sessions.

 

I would like to calculate the average of the top2 when I apply year, track ,session adn driver segmenters. For example when selecting 2023, Barcelona, session 1 and driver A. I want to get 222,5 :

 

I have applied the following code but the result is not correct:

TOPN_TOP_SPEED_AVRG_D =

  VAR  _topn_speed_average =

  AVERAGEX(

            TOPN(

                'TOPN'[Valor de TOPN];      

                ALLSELECTED(Data[SPEED]));

                Data[SPEED];

                DESC

            );

            Data[SPEED]

    )

VAR result =

CALCULATE(_topn_speed_average;

        KEEPFILTERS(Data[YEAR]);

        KEEPFILTERS(Data[TRACK]);

        KEEPFILTERS(Data[SESSION]);

        KEEPFILTERS(Data[DRIVER])

        )

RETURN result

(// Valor de TOPN =2)

Thank you very much for your help!

[Anonymous · Accepted solution]

Hi @Cris22 , lbendlin, thank you for your prompt reply!

Make your filter on page then show the average value in card as shown below:

Top2SpeedAverage = 
VAR TopSpeeds =
 
       TOPN(
           2,
           ALLSELECTED('Table'),
           'Table'[SPEED],
           DESC
       )
   
RETURN
   AVERAGEX(TopSpeeds, 'Table'[SPEED])

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Cris22]

Dear Joy,

Thank you very much for your answer, I was using visual data segmentations on the panel, I wanted the user to chose the segmentation and the topn number. I was able to make the average work somehow. Thank u!

[Anonymous]

Hi @Cris22,

If an answer has been helpful, please consider accepting the answer to help increase visibility of this question for other members of the Microsoft fabric community.

Thank you for your understanding.

[Cris22]

Good afternoon,

Thanks for your help, with what you told me, and tracing the problem, I came to the conclusion that my problem was that the topn was returning more than n values as there were some repeated values. As I have not really managed to solve it using formulas in dax, after much thought, I have had no choice but to add to the data an index column and another [modified speed] = [speed]+[index]/100000000.

Thank you 

[lbendlin]

Remember you are operating in the current filter context (assuming you use a measure) so there is no need to specify extra filters.

All you need is to divide the sum of the top two speed entries by 2 (or do an averagex if so inclined)