Customise subtotals in a matrix

[Anonymous]

I have a matrix below in Power BI and I want to display the subtotal as SUBTRACTION instead of SUM of the values. 

I'm using the following measure but it is displaying var b as negative in the grid. Is there a way to show the difference without impacting other values in the grid?

[Anonymous]

Hi @Anonymous , 

Please have a try.

Create a table includes Year Fields. 

Table 2 = VALUES('Table'[Year])

Then create a measure.

Measure =
VAR _a =
    CALCULATE (
        MAX ( 'Table'[Sales] ),
        FILTER (
            'Table',
            [Portfolio] = "A"
                && [Year] = SELECTEDVALUE ( 'Table 2'[Year] )
        )
    )
VAR _b =
    CALCULATE (
        MAX ( 'Table'[Sales] ),
        FILTER (
            'Table',
            [Portfolio] = "B"
                && [Year] = SELECTEDVALUE ( 'Table 2'[Year] )
        )
    )
VAR result =
    CALCULATE (
        MAX ( 'Table'[Sales] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Portfolio]
                IN VALUES ( 'Table'[Portfolio] )
                    && 'Table'[Year] IN VALUES ( 'Table 2'[Year] )
        )
    )
VAR _answer_ =
    IF ( result <> BLANK (), result, 0 )
RETURN
    IF ( ISINSCOPE ( 'Table'[Portfolio] ), _answer_, _b - _a )

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Anonymous ,

I have created a simple sample, please refer to it to see if it helps you.

Create a measure.

Measure =
VAR _2020 =
    CALCULATE (
        MAX ( 'Table'[VALUE] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[DATE] = 2020
                && 'Table'[subject] = SELECTEDVALUE ( 'Table'[subject] )
        )
    )
VAR _2021 =
    CALCULATE (
        MAX ( 'Table'[VALUE] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[DATE] = 2021
                && 'Table'[subject] = SELECTEDVALUE ( 'Table'[subject] )
        )
    )
RETURN
    IF ( HASONEVALUE ( 'Table'[DATE] ), MAX ( 'Table'[VALUE] ), _2020 - _2021 )

If I have misunderstood your meaning, please provide your desired output and your pbix without privacy information.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Here, we have to display the values of both variables a and b. Also the difference between the two in the subtotals.

[Anonymous]

Hi @Anonymous , 

Please have a try. 

Create a measure.

Measure = 
VAR _a =
    CALCULATE (
        MAX ( 'Table'[VALUE] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[DATE] = 2020
                && 'Table'[subject] = SELECTEDVALUE ( 'Table'[subject] )
        )
    )
VAR _b =
    CALCULATE (
        MAX ( 'Table'[VALUE] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[DATE] = 2021
                && 'Table'[subject] = SELECTEDVALUE ( 'Table'[subject] )
        )
    )
RETURN
    IF ( HASONEVALUE ( 'Table'[DATE] ), MAX('Table'[VALUE]),_a&" "& _b&" "  & " "&  (_a-_b))

If I have misunderstood your meaning, please provide your desired output and your pbix without privacy information.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Input data:

Expected Output:

[amitchandak · Accepted solution]

@Anonymous , You can use isinscope to replace the subtotals and totals

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

[Anonymous]

Does it not require a hierarchy?