Compute difference between rows and display as sub-total

[nimitchell]

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

[v-shex-msft]

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

[nimitchell]

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

[v-shex-msft · Accepted solution]

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

[nimitchell]

Could you please explain how this solution is actually working? Perhaps with some comments in the code?

[v-shex-msft]

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

[nimitchell]

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  🙂