Forum Discussion

Cris22's avatar
Cris22
Frequent Visitor
1 year ago
Solved

Column topn average

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!
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

5 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    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)

  • Cris22's avatar
    Cris22
    Frequent Visitor
    1 year ago

    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 

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      Cris22
      Frequent Visitor
      1 year ago

      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's avatar
        Anonymous
        Not applicable
        1 year ago

        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.