Min/max/AVG by filters data

[Jeremy19]

Hello,

I have two tables "DB" 

Name	Var1	Date	Condition
A	1000	43831	C1
A	1100	43832	C1
A	1362	43831	C2
A	740	43832	C2
B	1237	43831	C1
B	1354	43832	C2
B	932	43836	C2
B	922	43837	C1
C	788	43832	C2
C	892	43833	C2
C	1086	43834	C1
D	1363	43835	C1
D	1555	43838	C2

 and Player

Name	Position
A	G1
B	G1
C	G1
D	G2

I make a report by player so I filter my page by a name column. However, I would like to have measures that return the min / max / average of the maximum of players in the same position as that of the selected player. Is it possible ? In addition, only on condition C1. For exemple if I select player A, min =1086, avg=1141 and max=1237.

Thanks !!

[v-lionel-msft]

Hi @Jeremy19 ,

" In addition, only on condition C1. For exemple if I select player A, min =1086, avg=1141 and max=1237."

Regarding the example you gave, I can’t understand how the maximum, minimum and average values are calculated. Could you explain it with mathematical formulas?

Best regards,
Lionel Chen

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

[Jeremy19]

Ok,

Step 1 : select the maximum values per player of the same position and for condition C1. If I select a player who has the G1 position : 1100 for A, 1237 for B and 1086 for C. 

Step 2 : Three measures to obtain the min / avg / max among the values of step 1

In my example if I select player D as he is alone in his group the final result will be 1363 for min, avg and the max.

[v-lionel-msft · Accepted solution]

Hi @Jeremy19 ,

Is this what you want? Or you want a "Player" slicer?

Best regards,
Lionel Chen

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

[Jeremy19]

Does anyone have a solution for this problem?

[Ashish_Mathur]

Hi,

Could you show the expected result please?

[Jeremy19]

The expected result is to have the 3 measurements as described to be able to construct a graph as below.

The lines representing min / max of the max of the players of the same position, the point the average and the bar the max value of the player.

[mahoneypat]

Please try this measure expression to get your desired result.  This one is the min, but you can adapt it for max and avg as needed.  Note this assumes you have no relationship between the tables and the Name column from the Positions table is used in the slicer.  Note I got a min of 922 since name B is in same position.  Replace Var1 and Positions with your actual table names.

Min Var1 =
VAR vThisName =
    VALUES ( Positions[Name] )
VAR vNamesSamePosition =
    CALCULATETABLE (
        VALUES ( Positions[Name] ),
        VALUES ( Positions[Position] ),
        ALL ( Positions[Name] ),
        NOT ( Positions[Name]
            IN vThisName )
    )
RETURN
    CALCULATE (
        MIN ( Var1[Var1] ),
        TREATAS (
            vNamesSamePosition,
            Var1[Name]
        ),
        Var1[Condition] = "C1"
    )

Pat

[Jeremy19]

Thank you very much, this is not far from what I need. No need for the option to ignore the player but I just need to remove the NOT. On the other hand the problem again is that I need the MIN but among the MAX of each player in the same position (1100,1237 or 1086). While here I have the min on all the values of the players of the same position.