Forum Discussion
Compute difference between rows and display as sub-total
I have created the following table using the Matrix object:
I would like to compute ("Value in Use" - "Carrying Amount") for each column e.g. the yellow parts in 2013, and display that difference where the orange blocks are. Example for 2013 in first category (EU/LA) - 4,552.27 - 3,688.77 = 863,5. the result should appear in the orange part directly under 4,552.27. This for each column.
Is this possible within the Matrix construct? Or would it be better to compute these differences in the Power Query parts as I read the data in? I currently have the data in a normal table, with each row containing either a Carrying Amount or a Value in Use.
EDIT: here is some (space separated) test data that I have been using...
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.176229
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
6 Replies
HI nimitchell,
Can you please provide some sample data to test?
For your scenario, you should write a measure to instead the original amount column and add condition to check the subtotal row.
Sample:
SubDiff = IF ( COUNTROWS ( 'Table' ) = COUNTROWS ( FILTER ( ALL ( 'Table' ), [Date].[Month] = MAX ( [Date].[Month] ) ) ) || COUNTROWS ( 'Table' ) = COUNTROWS ( FILTER ( ALL ( 'Table' ), [Date].[Month] = MAX ( [Date].[Month] ) && [Date].[Year] = MAX ( [Date].[Year] ) ) ), ABS ( CALCULATE ( SUM ( 'Table'[Amount] ), 'Table'[Type] = "b" ) - CALCULATE ( SUM ( 'Table'[Amount] ), 'Table'[Type] = "a" ) ), SUM ( 'Table'[Amount] ) )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
