OK, I can see why this one has been giving you so much trouble. The concept seems simple - just apply the Component Diff measure to each row, and then add a logic check so that if it’s the total row, sum the column. However in practice, two big problems - the filter context is nightmarishly complex to untangle and because of the nature of the summarization, HASONEVALUE doesn’t work as the logic check for the total row.
I’ve been working this one hard since you posted it and I think I’ve got the measure working properly, but there’s a weird quirk in the visual that I haven’t figured out yet. Wanted to share it with you now before things get even more confusing with the revised data set.
Here’s the measure, and I’ve checked it pretty extensively both via COUNTROWS() of the virtual table, as well as actually filtering the data itself based on the slicer values, exporting it to Excel and checking the totals.
Component Diff w Tot =
VAR vTable =
'All data new',
'All data new'[Material Description],
'All data new'[Component Material]
"CmpDiff", [Component Diff]
ALLSELECTED( 'All data new'[Material], 'All data new'[Material Description] ),
ALLSELECTED( 'Date Table'[Month Year] ),
ALLSELECTED( 'Plt Company'[Company] ),
ALLSELECTED( 'All data new'[Component Description] ),
ALLSELECTED( 'All data new'[Component Material Type] ),
ALLSELECTED( 'Mat Analyzer'[Major family desc] )
VAR TotalSum =
VAR Final =
ISINSCOPE('All data new'[Material Description] ), [Component Diff],
So far, so good. Now here’s the weird quirk in the visual I haven’t figured out yet. The three highlighted rows don’t actually exist in the data set for December 2019 (the selected value in the slicer). Interestingly, if you go into the filter pane and try to select the component material for the three highlighted rows, they’re not there either (as they shouldn’t be). I checked the visual interactions, and they seem fine - it’s almost as if the visual is just rendering improperly.
Other than that however, it seems to be working fine. Give this a review, and let me know how you’d like to proceed from here.
Hope this is helpful. I’ve attached a Google Drive link to my full solution file (too big to post directly on the forum), as well as the Excel file I used to check the total.