Forum Discussion

nimitchell's avatar
nimitchell
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's avatar
    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

Anonymous's avatar
Anonymous
Not applicable
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's avatar
nimitchell
Frequent Visitor
8 years ago

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

  • Anonymous's avatar
    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] )
)

     

     

    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