Forum Discussion
Helper IIIMin/max/AVG by filters data
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 !!
Hi Jeremy19 ,
Is this what you want? Or you want a "Player" slicer?
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 Replies
- mahoneypat
Microsoft Employee
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
Jeremy19Thank 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.
- Ashish_Mathur
Super User
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.
- Jeremy19
Does anyone have a solution for this problem?
v-lionel-msftCommunity Support
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 ChenIf 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
Hi Jeremy19 ,
Is this what you want? Or you want a "Player" slicer?
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
