Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 ?
Solved! Go to 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 ) )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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.
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 ) )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
YEA. IT WORKS FROM MY SIDE. THANK YOU SO MUCH !
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
User | Count |
---|---|
100 | |
87 | |
81 | |
76 | |
71 |
User | Count |
---|---|
113 | |
104 | |
100 | |
73 | |
65 |