Help with DAX

[BKA]

Hello 

I have following tabel, which I've created in Power BI: 

AveragesTable = 
SUMMARIZE (
    FILTER ( 
        'Benchmark', 
        'Benchmark'[Playing Time] >= 60 && 
        'Benchmark'[Træningspassets navn] = "League Match" &&
        'Benchmark'[Team] IN { "U15", "U17", "U19" }
    ),
    'Benchmark'[Spillernavn],
    'Benchmark'[Team],
    "Reference Matches", COUNTROWS(FILTER('Benchmark', 'Benchmark'[Playing Time] >= 60)),
        "Position",
    MAXX('Player Names & Positions', 'Player Names & Positions'[Position]),
    "Est. Total Distance",
    AVERAGEX (
        FILTER ( 
            'Benchmark', 
            'Benchmark'[Playing Time] >= 60 && 
            'Benchmark'[Træningspassets navn] = "League Match" &&
            'Benchmark'[Team] IN { "U15", "U17", "U19" }
        ),
        [Estimated Total Distance pr. 90-minutes]
    )
    -- Include other "Estimated..." measurements here
)

This DAX code creates a summarized table called "AveragesTable" based on certain conditions and calculations from the 'Benchmark' table:

1. The FILTER function is used to filter the 'Benchmark' table based on conditions. The conditions include that the playing time should be greater than or equal to 60, the training asset name should be "League Match," and the team should be either "U15," "U17," or "U19."

2. The SUMMARIZE function groups the filtered data by 'Spillernavn' (player name), 'Team,' and 'Position.' It calculates the count of rows where playing time is greater than or equal to 60 to get the "Reference Matches."

3. The MAXX function is used to find the maximum value of the 'Position' from the 'Player Names & Positions' table, essentially determining the player's position.

4. The AVERAGEX function calculates the average estimated total distance for each player based on the filtered conditions. It further filters the 'Benchmark' table to only include data that meets the specified criteria.

This Tabel looks like this: 

Spillernavn	Team	Est. Total Distance	Reference Matches	Position
Hans Hansen	U17	11651	3	Midfielder
Hans Hansen	U15	11334	13	Midfielder
Ben Bensen	U17	11472	18	Midfielder
Ben Bensen	U15	10904	3	Midfielder
Thomas Thomasen	U17	12218	4	Midfielder

Now: I want to replace the Data if the player has played less than 5 Reference Matches. Dataen should be replaced with the average of (in this case) all Midfielder of the same Team. For example: Hans Hansen (U17) has only 3 Reference Matches - so his data should be replaces with the average of all Midfielders from U17 (which in this case is: (11651+11472+12218)/3 =11780). Furthermore the players "Reference Match should be replaced with the matches used to calculate the Midfielder Average (so: 3+18+4= 25). 

Can somebody help with that?

[Anonymous · Accepted solution]

Hi @BKA ,

The easiest way to do this is to add two calculated columns to the 'AveragesTable'.

Column 1 = IF('AveragesTable'[Reference Matches]<5,CALCULATE(AVERAGE('AveragesTable'[Est. Total Distance]),FILTER(ALL('AveragesTable'),'AveragesTable'[Team]=EARLIER('AveragesTable'[Team]))),'AveragesTable'[Est. Total Distance])

Column 2 = IF('AveragesTable'[Reference Matches]<5,CALCULATE(SUM('AveragesTable'[Reference Matches]),FILTER(ALL('AveragesTable'),'AveragesTable'[Team]=EARLIER('AveragesTable'[Team]))),'AveragesTable'[Reference Matches])

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum