Forum Discussion
Generating a Lookup Table Help
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):
| 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:
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!
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
3 Replies
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 AzamThank you so much for your kind words! I’m glad I could help. 😊
