How to find average from 2 different table

[Anonymous]

hello Guys, Hope you are doing well. 

May I know how can I calculate the average score from 2 different table that have one to many relationship ? kindly refer to the example below. 

Table in yellow have one to many relantionship to both green color table. 
The expecting result is, 

Suplier 1 should be (10+10+ 20)/3

Supplier 2 should be (20+20)/2

Supplier 3 should be (30+30)/2

May I know how can i create a new column in data view and generate the result as indicate below ? 

[Zach98]

A procurement category refers to a specific grouping or classification of goods, services, or supplies that are procured by an organization. Categories are typically created based on similarities in terms of the type of product or service, industry, function, or strategic importance. The purpose of categorizing procurement is to facilitate effective management, analysis, and strategic decision-making within each specific area of spend.

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new column.

Avg expected result CC = 
DIVIDE (
    SUMX ( RELATEDTABLE ( Score1 ), Score1[Score] )
        + SUMX ( RELATEDTABLE ( Score2 ), Score2[Score] ),
    COUNTROWS ( RELATEDTABLE ( Score1 ) ) + COUNTROWS ( RELATEDTABLE ( Score2 ) )
)

[Anonymous]

YEA. IT WORKS FROM MY SIDE. THANK YOU SO MUCH !

[MahyarTF]

Hi,

In Power Query, you could Append two tables as new or in one of them,

Then use the created table and bring the "Supplier name" and average of Score in your visual :

Appreciate for Kudos