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.
Im attempting to calculate average scores based on a column within the table. The kicker is that I'd like to filter down what values are included in the averages -> using a related table. An example of the tables are below:
Reference table (used to relate the names together in the subsequent tables)
Name Ref | |
Name1 | Name2 |
Player1 | Player1 |
Player2 | Player2 |
Player3 | Player3 |
Player4 | Player4 |
Player5 | Player5 |
reference table : used to collect history of scores)
2021Scores | ||
Name2 | Pos | Points |
Player 1 | Pos1 | 10 |
Player 1 | Pos1 | 15 |
Player 1 | Pos1 | 13 |
Player 2 | Pos2 | 1 |
Player 2 | Pos2 | 10 |
Player 3 | Pos1 | 5 |
Player 3 | Pos1 | 20 |
Player 3 | Pos1 | 12 |
Player 4 | Pos3 | 8 |
Player 4 | Pos3 | 7 |
Player 5 | Pos2 | 4 |
Player 5 | Pos2 | 20 |
Activity (active players that I want to do calculations for)
Games | ||
Name1 | Pos | Team |
Player 3 | Pos1 | Team1 |
Player 2 | Pos2 | Team2 |
Player 5 | Pos2 | Team3 |
The Games and 2021Scores tables are related to the Name Ref table using 1 to 1 relationships to tie these tables together - no probs here.
In the 2021Scores table, I want to find the average value per position. Pos3 has 2 rows on the table (7 and 😎 and therefore Pos3 should report an average of 7.5, and so forth for any position listed. I was able to produce a custom column that accomplishes this by itself -> no prob here either, but note, this calculates an average across the entire 2021scores table.
Average Pts Per Pos =
CALCULATE (
AVERAGE ( '2021scores'[points] ),
FILTER ( ALLSELECTED ( '2021scores' ), '2021scores'[Pos] = EARLIER('2021scores'[Pos]))
)
However what I would like to do -> can I produce these averages only using rows where the player name also exists in the Games table?
In the example above, the Games table had player3, player2 and player5. Therefore I want to calculate the average scores for those players and summarized up to the position level too using only those 3 players as the data set. Any help is appreciated!
Solved! Go to Solution.
Hi, @sedric1 ;
Is your problem solved? If so, Would you mind accept the helpful replies as solutions?
If not, you also could use ALLEXPECT().such as:
averge = CALCULATE(AVERAGE('2021Scores'[Points]),ALLEXCEPT('2021Scores','2021Scores'[Name2],'2021Scores'[Pos]))
Or
Column = CALCULATE(SUM('2021Scores'[Points])/COUNT('2021Scores'[Name2]),FILTER('2021Scores',[Name2]=EARLIER(Games[Name1])))
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @sedric1 ;
Is your problem solved? If so, Would you mind accept the helpful replies as solutions?
If not, you also could use ALLEXPECT().such as:
averge = CALCULATE(AVERAGE('2021Scores'[Points]),ALLEXCEPT('2021Scores','2021Scores'[Name2],'2021Scores'[Pos]))
Or
Column = CALCULATE(SUM('2021Scores'[Points])/COUNT('2021Scores'[Name2]),FILTER('2021Scores',[Name2]=EARLIER(Games[Name1])))
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
For anyone following this thread - > i believe I got it working and the fundamental piece I was missing is that I needed to create a summarized table first so that I can filter one table based on the other, and then create the calculated column to group the results and aggregate on those groups. Think I'm ok to roll now.
@sedric1 , In a column allselected has no role as it will not take filter value.
You can try a new column like
Average Pts Per Pos =
CALCULATE (
AVERAGE ( '2021scores'[points] ),
FILTER ( ( '2021scores' ), '2021scores'[Pos] = EARLIER('2021scores'[Pos]) && '2021scores'[Name2] = Games[Name1])
)
Thanks for the response @amitchandak
I removed that allselected function per your post. The one thing I am not able to get working is the conditional name statement -> && '2021scores'[Name2] = Games[Name1], since there are two different tables in one filter statement. I see there are some related posts regarding filtering across multiple tables so I will review them and see if I can put this 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
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |