Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Jeremy19
Helper III
Helper III

Min/max/AVG by filters data

Hello,

 

I have two tables "DB" 

NameVar1DateCondition
A100043831C1
A110043832C1
A136243831C2
A74043832C2
B123743831C1
B135443832C2
B93243836C2
B92243837C1
C78843832C2
C89243833C2
C108643834C1
D136343835C1
D155543838C2

 and Player

NamePosition
AG1
BG1
CG1
DG2

 

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

Hi @Jeremy19 ,

 

v-lionel-msft_0-1613035466885.png

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.

 

View solution in original post

8 REPLIES 8
v-lionel-msft
Community Support
Community Support

Hi @Jeremy19 ,

 

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

 

v-lionel-msft_2-1612422238072.png

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.

 

 

 

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.

Hi @Jeremy19 ,

 

v-lionel-msft_0-1613035466885.png

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
Helper III
Helper III

Does anyone have a solution for this problem?

Ashish_Mathur
Super User
Super User

Hi,

Could you show the expected result please?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Jeremy19_0-1612253958815.png

 

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
Microsoft Employee
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

 





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

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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