Average of group level data

[SMcCullagh]

Hi guys,

Please see below the DAX measure I am working with.

I want to evaluate a players total distance for the selected date as a % of match total distance (max or average dependent on slicer selection). I then want to avearge each players result for the position group. I only want the players that have a value for the selected date to be included in any of the calculations.

If a player's average or max match total distance is 0, I would like for the result to be zero and included in the group average.

If a player's average or max match total distance is BLANK, I would exclude from the group average.

For example in image below, I would expect the result to be 86 + 71 + 90 + 81 + 86 = 414. Divided by 5 = 82.8%

Session MD% Total Distance =

VAR PlayerSum =

    CALCULATE(

        SUM('GPS Data'[Total Distance]),

        ALLEXCEPT(Players, Players[Name])

    ) // This is where I want to show a players total distance for the selected date

VAR avg_ = CALCULATE(

    AVERAGE('GPS Match Data'[Total Distance]),

    ALL(Fixtures),

    ALL(Dates),

    'GPS Match Data'[Match Mins] >= 50

) // This is where I am looking to reference a players average match total distance

VAR max_ = CALCULATE(

    MAX('GPS Match Data'[Total Distance]),

    ALL(Fixtures),

    ALL(Dates),

    'GPS Match Data'[Match Mins] >= 50

) // This is where I am looking to reference a players max match total distance

VAR PlayerResult =

    SWITCH(

        SELECTEDVALUE('AVG/MAX'[AVG/MAX], "Average"),

        "Average", DIVIDE(PlayerSum, avg_),

        "Max", DIVIDE(PlayerSum, max_)

    ) // Dependent of slicer selection ('Average' or 'Max') the caclulation should change

VAR GroupResult =

    AVERAGEX(

        VALUES(Players[Name]),

        SWITCH(

            SELECTEDVALUE('AVG/MAX'[AVG/MAX], "Average"),

            "Average", DIVIDE(

                CALCULATE(SUM('GPS Data'[Total Distance]), ALLEXCEPT(Players, Players[Name])),

                avg_

            ),

            "Max", DIVIDE(

                CALCULATE(SUM('GPS Data'[Total Distance]), ALLEXCEPT(Players, Players[Name])),

                max_

            )

        )

    )  

RETURN  

IF(

    ISINSCOPE(Players[Name]),

    PlayerResult,

    GroupResult

)

Below is the outcome of the current DAX.

Thanks in advance.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...