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

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