It’s all about evaluation context. When you drop the measure in the table, the context over which it is evaluating is clear (Review No). However, in a card there is no evaluation context. In case with insufficient evaluation context, DAX will do it’s best to guess, which is why sometimes you get a right answer, sometimes a wrong answer and sometimes a blank, which is DAX’s equivalent of
¯\_(ツ)_/¯.
Thus, for these cards (and for the total row of the table for the Lvl 5 measure), we need to provide the necessary evaluation context within the measure through the use of a virtual table.
Here’s the card measure for AVERAGEX:
AverageX Card =
VAR vTable =
ADDCOLUMNS(
DISTINCT( 'Contract Rev2'[Review No] ),
"@Lev5Tot", [Level5 Total]
)
VAR Result =
AVERAGEX(
vTable,
[@Lev5Tot]
)
RETURN
Result
vTable recreates the table below virtually within the measure and holds it in a variable for future use:
That future use then comes in the next variable, which takes the sum of the Level 5 measure from the virtual table. The exact same principles are applied in the SumX Card and CountX card measures (although DAX got the count correct, I like to be sure of what it’s doing so I included a CountX measure.
The power of evaluation context (and iteratring functions) can be seen when you drop the SumX card measure back into the visual containing the Review No context:
It evaluates each row in context, returning the proper value, and then when it gets to the total row (without any context), it evaluates over the entire virtual table - in effect treating that total row as it did the card measure.
As you move forward in DAX, these are critically important concepts to master. Fortunately, @sam.mckay has a ton of fantastic content on evaluation, filter and row context, virtual tables and fixing incorrect totals. Also, @Greg has put together an exceptional compendium of portal and forum resources on the incorrect totals problem in the DAX patterns section of the forum:
He points to a lot of the videos of Sam’s that I would recommend you watch, so that might be the best place to start.
I hope this is helpful. Revised solution file posted below (new stuff is on p. 2)
- Brian
eDNA Forum - Level 5 SUM Solution Rev2.pbix (33.7 KB)