Create a measure to compute the average of sum with some conditions

[maxduff59]

Hello everybody,

I am really struggling understanding and realizing measures. I have the following dataset : 

And I would like to compute the average of the sum of the total distance in a week for each position. 

 I tried to create this following mesure but I think that I am missing some informations.

Moyenne Semaine =
AVERAGEX(
SUMMARIZE('Stats_Activités'  
      , 'Stats_Activités'[Year_Week]  
      , 'Stats_Activités'[position_name]  
      , "Distance courue", SUM('Stats_Activités'[total_distance_courue_(m)])  
      ),[Distance courue])

 Thanks in advance for your help and your time 

[v-yinliw-msft · Accepted solution]

Hi @maxduff59 ,

You can try this method:

Table =

SUMMARIZE (

    'Stats_Activités',

    'Stats_Activités'[Year_Week],

    'Stats_Activités'[Position],

    'Stats_Activités'[Joueur],

    "SUM",

        CALCULATE (

            SUM ( 'Stats_Activités'[Distance] ),

            FILTER (

                'Stats_Activités',

                'Stats_Activités'[Position] = EARLIER ( 'Stats_Activités'[Position] )

                    && 'Stats_Activités'[Year_Week] = EARLIER ( 'Stats_Activités'[Year_Week] )

            )

        )

)

Table 2 =

SUMMARIZE (

    'Table',

    'Table'[Position],

    'Table'[Joueur],

    "AUG",

        CALCULATE (

            AVERAGE ( 'Table'[SUM] ),

            FILTER (

                'Table',

                'Table'[Position] = EARLIER ( 'Table'[Position] )

                    && 'Table'[Joueur] = EARLIER ( 'Table'[Joueur] )

            )

        )

)

Table 3 =

SUMMARIZE (

    'Table 2',

    'Table 2'[Position],

    "AUG",

        CALCULATE (

            AVERAGE ( 'Table 2'[AUG] ),

            FILTER ( 'Table 2', 'Table 2'[Position] = EARLIER ( 'Table 2'[Position] ) )

        )

)

The result is:

Hope this helps you.

Here is my PBIX file.

Best Regards,

Community Support Team _Yinliw

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

[maxduff59]

Thanks a lot. It seems great. 
I just had a little question about the use of EARLIER function in the filter, why do you use that function ?

@v-yinliw-msft 

[v-yinliw-msft]

Hi @maxduff59 ,

This function is used to be able to get the context of the same position.

Best Regards,

Community Support Team _Yinliw

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

[amitchandak]

@maxduff59 , The measure seem correct to me, It will avg other than week position

may be you need

Moyenne Semaine =
AVERAGEX(
SUMMARIZE('Stats_Activités'
, 'Stats_Activités'[Year_Week]
, "Distance courue", SUM('Stats_Activités'[total_distance_courue_(m)])
),[Distance courue])

or Switch that is based on isfiltered or isinscope

IsInScope - Switch Rank at different levels: https://youtu.be/kh0gezKICEM

HasOneValue , Isfiltered: https://www.youtube.com/watch?v=hXg3kRFSGjA&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=38