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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sedric1
Helper III
Helper III

I want to calculate an average from a filtered table using another table

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
Name1Name2
Player1Player1
Player2Player2
Player3Player3
Player4Player4
Player5Player5

reference table : used to collect history of scores)

2021Scores
Name2PosPoints
Player 1Pos110
Player 1Pos115
Player 1Pos113
Player 2Pos21
Player 2Pos210
Player 3Pos15
Player 3Pos120
Player 3Pos112
Player 4Pos38
Player 4Pos37
Player 5Pos24
Player 5Pos220

Activity (active players that I want to do calculations for)

Games
Name1PosTeam
Player 3Pos1Team1
Player 2Pos2Team2
Player 5Pos2Team3

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!

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

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:

vyalanwumsft_0-1637895883153.png

vyalanwumsft_1-1637895903777.png

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.

View solution in original post

4 REPLIES 4
v-yalanwu-msft
Community Support
Community Support

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:

vyalanwumsft_0-1637895883153.png

vyalanwumsft_1-1637895903777.png

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.

sedric1
Helper III
Helper III

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.

amitchandak
Super User
Super User

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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