Generating a Lookup Table Help

[WishAskedSooner]

HI Experts!

I am having difficulty generating a lookup table that I need for later processing in a measure. Here is a sample of my requirement and the problem.

I have the following table called 'Fact':

EID	AID	VAL1	VAL2	VAL3
1	3	0.1	0.05	0.025
1	4	0.2	0.1	0.05
1	5	0.3	0.15	0.075
2	3	0.4	0.2	0.1
2	4	0.5	0.25	0.125
2	5	0.6	0.3	0.15
3	3	0.7	0.35	0.175
3	4	0.8	0.4	0.2
3	5	0.9	0.45	0.225

Step 1: Calculate a needed temporary column

This involves adding the following column via the following DAX (for some reason the code format has disappeared from my editor, so I apologize for the poor formatting):

    VAR _NewVal =

        ADDCOLUMNS(

            'Fact',

            "NEWVAL",

            CALCULATE( SUM( 'Fact'[VAL1] ) )

            + CALCULATE( SUM( 'Fact'[VAL2]) ) * 0.1

            + CALCULATE( SUM( 'Fact'[VAL3] ) ) * 0.01

        )

which results in the following table:

EID	AID	VAL1	VAL2	VAL3	NEWVAL
1	3	0.1	0.05	0.025	0.10525
1	4	0.2	0.1	0.05	0.2105
1	5	0.3	0.15	0.075	0.31575
2	3	0.4	0.2	0.1	0.421
2	4	0.5	0.25	0.125	0.52625
2	5	0.6	0.3	0.15	0.6315
3	3	0.7	0.35	0.175	0.73675
3	4	0.8	0.4	0.2	0.842
3	5	0.9	0.45	0.225	0.94725

Step 2: Summarize by AID and calculate average NEWVAL for each AID

The final step is to generate the following lookup table which is the average of the above table by AID on NEWVAL:

Needed Table:

AID	AVG NEWVAL
3	0.421
4	0.52625
5	0.6315

 I have tried the following DAX but am getting the wrong values which is just the average of every NEWVAL:

    VAR _AvgNewVal =

        ADDCOLUMNS(

            SUMMARIZE(

                _NewVal,

                'Fact'[AID]

            ),

            "AVG NEW VAL",

                AVERAGEX( _NewVal, [NEWVAL] )

        )

Current Results (incorrect):

AID	AVG NEWVAL
3	0.52625
4	0.52625
5	0.52625

I am stuck and not sure what to do next. All your help is greatly appreciated!

[Nasif_Azam · Accepted solution]

Hey @WishAskedSooner ,

It looks like the issue is coming from how the AVERAGEX function is being used. Try the DAX expression:

VAR _AvgNewVal =
    SUMMARIZE(
        'Fact',
        'Fact'[AID],
        "AVG NEW VAL", AVERAGEX(
            FILTER(
                _NewVal, 
                'Fact'[AID] = EARLIER('Fact'[AID])
            ),
            [NEWVAL]
        )
    )

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[WishAskedSooner]

@Nasif_Azam,

That is some DAX Ninja! Thanks so much. Marking as a solution and much kudos!

[Nasif_Azam]

Thank you so much for your kind words! I’m glad I could help. 😊