Lookup value and calculate Average

[alicelpn]

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

[daxer-almighty]

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

[AlB]

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.

[alicelpn]

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

[AlB]

@alicelpn 

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.

[alicelpn]

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]

@alicelpn

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.

[alicelpn]

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

[AlB · Accepted solution]

@alicelpn 

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.