Count Level 5 Total =
CALCULATE(
COUNTX('Contract Review', [Level 5 Total])
)
Level 5 SUMX Total =
CALCULATE(
SUMX('Contract Review', [Level 5 Total])
)
Average Level 5 Total =
CALCULATE(
AVERAGEX('Contract Review', [Level 5 Total])
)
Ironically, when i did a COUNTX on your Level 5 Total measure in the pbix you attached, it worked.
Even though we only actually have 2 matching records for the Level 5 Total measure as you can see it returned 18 as the count, which was the total number of records, that matched in your table image, which would be incorrect also under the context shown.
The table below shows Level 4 and Level 5 side by side.
the Level 4 DAX measure what the earlier solution you provided. One thing i did notice was that the level 4 column has a totals row, where level 5 doesn’t. Any idea why is can’t get the iteration functions Countx, Averagex, or Sumx to work for Level 5
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)
Thanks you very much for your assistance, guidance and the reference material, you are a gem!
I obviously have lot’s to learn and understand, as I say if I can learn something new each day, retain what I’ve learned, and apply consistently, I call it continuous improvements!
My pleasure - definitely a mutually beneficial process. The “tough nuts to crack” on the forum are great practice and the means by which I’ve learned the bulk of what I know after building the foundation from @sam.mckay’s videos.
To your point about learning/practice/continuous improvement, you may find the strategies in this video useful:
