Forum Discussion
How to find average from 2 different table
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 ?
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 ) ) )
4 Replies
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
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 ) ) )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.
