Forum Discussion
Lookup value and calculate Average
Hi there,
I need help and advices especially the DAX syntax to achieve the following:
1. to lookup the value in Table 1 exist in the Table 2 (master data) and captured in Exist column of Table 1. For e.g. the sample data in 2 tables
Table 1 Table 2
Name Role Name Element Name Exist Category Score Role Name Element Name
Ann Role A Element A Yes Functional 1 Role A Element A
Matt Role B Element F No Functional 1 Role A Element B
Ann Role A Element B Yes Functional 0 Role B Element C
Ann Role A1 Element x Yes Non-Function 1 Role A1 Element x
Ann Role A Element D Yes Functional 0 Role A Element D
Ann Role A Element y Yes Functional 1 Role A Element y
Ron Role B Element G Yes Functional 1 Role B Element G
Role A1 Element z
Role A1 Element m
2. To get the average score for each person by their (Role, Exist = Yes and Category)/total elements in the role. Expected result for;
Ave score for Ann for Role A, Element Existed & is Functional = 2/5 => 40%
Ave score for Ann for Role A, Element Existed & is Non-Function = 1/3 => 33.3%
Ave score for Ron for Role B, Element existed & is Functional = 1/2 => 50%
Ave score for Matt for Role B, Element existed & is Functional = 0
Thank you heaps!!
8 Replies
- AlB
Community Champion
Hi alicelpn
I do not understand how you are calculating the averages. Can you elaborate on one of them, for instance:
Ave score for Ann for Role A, Element Existed & is Functional = 2/5 => 40%
Can you show which rows go into the 2 (numerator) and which into the 5 (denominator)? And the rationale behind it
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.
my sincere apology for the mistakes made and confusion caused. i redo the expected result for the average score as below. thanks heap
- AlB
See 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.
- daxer-almighty
Solution Sage
[Exists] = // calculated column var RoleName_ = Table1[Role Name] var ElementName_ = Table1[Element Name] var RoleElementCombinationExists = COUNTROWS( filter( Table2, Table2[Element Name] = ElementName_ && Table2[Role Name] = RoleName_ ) ) > 0 return if( RoleElementCombinationExists, "Yes", "No" ) [Average] = // measure // If you start slicing and dicing, you'll // figure out how this measure works. AVERAGEX( SUMMARIZE( Table1, Table1[Name], Table1[Role Name] ), var Numerator = CALCULATE( SUM( Table1[Score] ) ) var Denominator = CALCULATE( SUM( Table1[Score] ), ALLEXCEPT( Table1, Table1[Name], Table1[Role Name] ) ) return divide( Numerator, Denominator ) )Now that I've tried to implement this on a real model I realized that it makes no sense. Your calculations are not making sense. You'll have to be more precise, alicelpn. Sorry!
