cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper III

## Min/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 !!

1 ACCEPTED SOLUTION
Community Support

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.

8 REPLIES 8
Community 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 Chen

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

Helper III

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.

Community Support

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.

Helper III

Does anyone have a solution for this problem?

Super User

Hi,

Could you show the expected result please?

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper III

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.

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Helper III

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.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors