cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
alicelpn
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
AlB
Super User III
Super User III

@alicelpn 

Not sure I understand, but see attached.

 

SU18_powerbi_badge

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.

 

 

View solution in original post

8 REPLIES 8
daxer-almighty
Solution Sage
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!

 

AlB
Super User III
Super User III

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

 

SU18_powerbi_badge

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

alicelpn_0-1618740008807.png

 

AlB
Super User III
Super User III

@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" )

 

SU18_powerbi_badge

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

 

AlB
Super User III
Super User III

@alicelpn

Looks like there's still a mistake for Ann,  RoleA1. Should be 1/3. Updated Measure and attached file

 

SU18_powerbi_badge

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
Frequent Visitor

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

 

alicelpn_0-1618843215538.png

 

 

AlB
Super User III
Super User III

@alicelpn 

Not sure I understand, but see attached.

 

SU18_powerbi_badge

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.

 

 

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

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

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors