@axaeffect,
Iâll try to answer all of your questions above and in this post, since they are closely related.
I can tell from the problems youâre having with your measures that you need to develop a better understanding of evaluation context, filter context and row context. These are the foundational principles of all DAX calculations, and even with focused study they take a while to fully sink in. I think the best place to start is with this course:
focusing particularly on all the sections related to context, CALCULATE, and table functions. If youâre anything like me, youâll probably have to watch these over and over and over again as you work through your Power BI projects. However, one day it all will click into the place, and youâll feel like this:
The next foundational concept to focus on is âmeasure branchingâ. In your second post, you are on the right track, but youâre trying to do too much within a single measure. In measure branching, each measure typically does one specific thing, and they build logically upon each other, in increasing complexity. This video does a great job explaining the concept and providing examples:
OK, on to your specific questions:
- What is the âAging with Totalâ measure doing?
- filtering your fact table, based on the selected date range harvested from your slider
- making a virtual copy of your filtered fact table
- adding a virtual column to 2) with the results of your Aging measure for each row of the virtual table
- checking to see if each row of the matrix has a single value for the SalesOrderNumber field
- if yes (which will be true for every row that isnât the final total row), it just applies the Aging measure
- if no (meaning we are in the total row of the matrix), it takes the sum of the entire column virtual aging column we added in step 3)
This is all necessary since there is no proper evaluation context for the regular Aging measure to operate on in the total row.
- What is SELECTEDVALUE doing?
Thereâs not a specific Enterprise DNA video on this function, but I think the following provides a good explanation:
- Aging Category Measure
You had the right idea here, but didnât need the CALCULATE statement, nor the variable/SELECTEDVALUE construct. In this case, just let the evaluation context and measure branching do most of the work, and use a simple IF statement:
Aging Category Revised =
IF(
[Aging with Total] <=1,
"small",
"big"
)
Hope this answers your questions. Full solution file posted below.
Work through the videos above, and if you run into other problems, just give a shout.