Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Solved! Go to 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
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
Thank you so much for your kind words! I’m glad I could help. 😊