cancel
Showing results for
Did you mean:
Frequent Visitor

## 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!!

1 ACCEPTED SOLUTION
Super User III

Not sure I understand, but see attached.

 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.

8 REPLIES 8
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!

Super User III

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.

Frequent Visitor

my sincere apology for the mistakes made and confusion caused.  i redo the expected result for the average score as below. thanks heap

Super User III

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.

Frequent Visitor

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 🙏🏻

Super User III

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.

Frequent Visitor

it works!!!! thank you very much 🤣😘.  However, can you help me with the question below?

Super User III

Not sure I understand, but see attached.

 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.

Announcements

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks