cancel
Showing results for
Did you mean:
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 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!

1 ACCEPTED SOLUTION
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:

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.

4 REPLIES 4
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:

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.

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.

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])
)

Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!
Helper III

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.

Announcements

#### The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

#### Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!