Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |