Forum Discussion
Lookup value and calculate Average
Community ChampionSee the attached file for a possible solution. Your percentages for Ann both seem wrong still. Should be 4/4 for RoleA and 1/3 for RoleA1. Anyway you can tweak the measure as needed.
Avg Score =
VAR numRowsTable2_ =
CALCULATE (
COUNT ( Table2[RoleName] ),
TREATAS ( DISTINCT ( Table1[RoleName] ), Table2[RoleName] )
)
RETURN
DIVIDE ( COUNT ( Table1[RoleName] ), numRowsTable2_ )Exist =
VAR aux_ =
CALCULATE (
COUNT ( Table2[ElementName] ),
FILTER (
Table2,
Table2[ElementName] = Table1[ElementName]
&& Table2[RoleName] = Table1[RoleName]
)
)
RETURN
IF ( aux_ > 0, "YES", "NO" )
|
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
Hello AIB,
Appreciated much your prompt response and help. The Exist work well, thank you.
My bad 🤦♀️ the formula for average = total score of Ann in Role A/total number of elements in Role A = 2/4 = 50% and to be appended in new table with the columns Name, Role Name, Average
Thanks 🙏🏻
- AlB
Looks like there's still a mistake for Ann, RoleA1. Should be 1/3. Updated Measure and attached file
Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
it works!!!! thank you very much 🤣😘. However, can you help me with the question below?