Forum Discussion
Compute difference between rows and display as sub-total
Hi nimitchell,
Please check below formula if it works on your side.
Measure:
Diff = IF ( COUNTROWS ( 'Sample data' ) = COUNTROWS ( FILTER ( ALL ( 'Sample data' ), [CapGrad] = MAX ( 'Sample data'[CapGrad] ) ) ) || COUNTROWS ( 'Sample data' ) = COUNTROWS ( FILTER ( ALL ( 'Sample data' ), [CapGrad] = MAX ( 'Sample data'[CapGrad] ) && [Year] = MAX ( 'Sample data'[Year] ) ) ), ABS ( CALCULATE ( SUM ( 'Sample data'[Value] ), 'Sample data'[Type] = "Car" ) - CALCULATE ( SUM ( 'Sample data'[Value] ), 'Sample data'[Type] = "Value" ) ), SUM ( 'Sample data'[Value] ) )Above formula only available on marking part, I haven't added any conditional to affect total column and total row.
Regards,
Xiaoxin Sheng
Thank you very much for the great answer. I have attempted to adapt it to my data but still have a few issues (the table is not showing as expected, even though the measure.
I have included data below that has the same structure as mine - could you help me adapt your solution please?
CapGrad is the columns, so split by S/U, Mercedes, etc., whicha re in turn drilled down into the Type (like your 'a' and 'b'. The columns are years. The difference I want to compute is then the corresponding Value for a set of CapGrad and Type in one year.
If you copy and paste into excel using tab as a delimiter it works. I will try to uplaod it another way too just in case that isn't helpful.
BestF CapGrad Type Year Value Tesla S/U Car 2016 1050.753275 Tesla S/U Car 2015 13.76775538 Tesla S/U Value 2016 2133.461169 Tesla S/U Value 2015 1785.887931 Tesla Mercedes Car 2016 154.0025548 Tesla Mercedes Car 2015 133.162 Tesla Mercedes Value 2016 243.5816935 Tesla Mercedes Value 2015 100.9351563 Tesla Mercedes + GC/NA Car 2016 688.2506933 Tesla Mercedes + GC/NA Car 2015 378.4544428 Tesla Mercedes + GC/NA Value 2016 879.0503796 Tesla Mercedes + GC/NA Value 2015 464.0001573 Tesla GC/NA + SFR Car 2016 100.991353 Tesla GC/NA + SFR Car 2015 29.6827127 Tesla GC/NA + SFR Value 2016 1169.492568 Tesla GC/NA + SFR Value 2015 394.1421524 Tesla Life Car 2016 48.02338352 Tesla Life Car 2015 102.5789022 Tesla Life Value 2016 28.83309994 Tesla Life Value 2015 274.9924524 First S/U Car 2016 193.3435293 First S/U Car 2015 22.18691285 First S/U Value 2016 57.35636377 First S/U Value 2015 91.61001358 First I/O Car 2016 4.888315691 First I/O Car 2015 110.4481171 First I/O Value 2016 141.8388878 First I/O Value 2015 168.2045724 First L/G Car 2016 64.43360298 First L/G Car 2015 107.0808728 First L/G Value 2016 26.6170231 First L/G Value 2015 60.10688546 First S/U Value 2016 193.173096 Tesla S/U Value 2016 381.6880641 Tesla Mercedes Value 2016 136.888348 Tesla Mercedes + GC/NA Value 2016 1615.797186 Tesla GC/NA + SFR Value 2016 876.8585662 Tesla Life Value 2016 452.1762297
Hi nimitchell,
Please check below formula if it works on your side.
Measure:
Diff =
IF (
COUNTROWS ( 'Sample data' )
= COUNTROWS (
FILTER ( ALL ( 'Sample data' ), [CapGrad] = MAX ( 'Sample data'[CapGrad] ) )
)
|| COUNTROWS ( 'Sample data' )
= COUNTROWS (
FILTER (
ALL ( 'Sample data' ),
[CapGrad] = MAX ( 'Sample data'[CapGrad] )
&& [Year] = MAX ( 'Sample data'[Year] )
)
),
ABS (
CALCULATE ( SUM ( 'Sample data'[Value] ), 'Sample data'[Type] = "Car" )
- CALCULATE ( SUM ( 'Sample data'[Value] ), 'Sample data'[Type] = "Value" )
),
SUM ( 'Sample data'[Value] )
)
Above formula only available on marking part, I haven't added any conditional to affect total column and total row.
Regards,
Xiaoxin Sheng
Thanks for the adaption, but unfortunately it doesn't work for me... I don't think it can be due to my data being slightly different - the structure is identical, only the names and values are altered and I of course used my correct names when applying your measure.
It seems as thought the IF statement is not getting into the interesting part of computing the difference. The table still simply shows the sum of 'Car' and 'Value', not the difference. Could you maybe explain a little bit of the logic behind your solution? Perhaps I can the adapt it myself. Why do you filter in the IF condition using: `[CapGrad] = MAX ( 'Sample data'[CapGrad] )`?
CapGrad is a text column, and the MAX function says that it ignores text.EDIT:
Your measure does work as desired! Thank you! My problem was, well, me. I had a filter missing, meaning there was an extra value in Type (not just the two I had given you in my data sample). It would still be useful for my learning if you could explain a few of the steps in your solution :-) Thanks again!
p.s - I do not need the extra condition for the Totals column :-)
Could you please explain how this solution is actually working? Perhaps with some comments in the code?
Hi nimitchell,
By default, matrix visual will auto summary values. Actually, my conditions also based on it.
It will check on current matrix summary result and manually filter result to apply the custom calculation on specific level.
For example:
Test 2 = IF ( COUNTROWS ( 'Sample data' ) = COUNTROWS ( FILTER ( ALL ( 'Sample data' ), [CapGrad] = MAX ( 'Sample data'[CapGrad] ) && [Year] = MAX ( 'Sample data'[Year] ) ) ), "Custom" , SUM ( 'Sample data'[Value] ) )If your visual contians some filter on it, you should use allselected to instead the all function, it will apply on current filter.
Wrong:
Right:
Regards,
Xiaoxin Sheng