topic Re: Lookup value and calculate Average in DAX Commands and Tips

Lookup value and calculate Average

alicelpn — Sat, 17 Apr 2021 15:52:08 GMT

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

Re: Lookup value and calculate Average

AlB — Sun, 18 Apr 2021 10:21:56 GMT

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.

Re: Lookup value and calculate Average

daxer-almighty — Sun, 18 Apr 2021 00:01:11 GMT

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

Re: Lookup value and calculate Average

alicelpn — Sun, 18 Apr 2021 10:01:17 GMT

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

Re: Lookup value and calculate Average

AlB — Sun, 18 Apr 2021 10:50:50 GMT

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.

Re: Lookup value and calculate Average

alicelpn — Sun, 18 Apr 2021 13:03:58 GMT

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

Re: Lookup value and calculate Average

AlB — Sun, 18 Apr 2021 21:44:41 GMT

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.

Re: Lookup value and calculate Average

alicelpn — Mon, 19 Apr 2021 14:39:41 GMT

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

Re: Lookup value and calculate Average

AlB — Mon, 19 Apr 2021 16:44:44 GMT

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.