Forum Discussion

Frequent Visitor

8 years ago

Solved

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 d...

Need Help

Anonymous

8 years ago

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

Frequent Visitor

8 years ago

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

Anonymous

Not applicable

8 years ago

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] )
)