Matrix Total Subtracted Value in Power BI Desktop

[Anonymous]

Dear Community Folks,

The matrix visualization in my report is summing up the measures by default on its Total. But, I wanted to have a difference (subtract) value on the Total. Please, ref the below screenshots and I have also attached .xlsx and .pbix files with this post for your kind ref.

Sample Excel File Link

Sample pbix File Link

We are creating a group in Matrix out of a single field as shown in the above screenshot.

We need to tally the Amount of Total assets and Amount of Liabilities & Equity to Zero.

Total = Sum of Total Assets - Sum of Total Liabilities & Equity

Total = 120 - 120 = 0

But, what we are getting in Power BI matrix is the summed value. Total = 240 as depicted in the above screenshot,

 Is there any way to get the subtracted value,

Total = 120 - 120 Total = 0.

Your timely help is much appreciated.

Many thanks,

Praveen.

[sjoshi]

Hi Cherie 

How do we apply time dimesion to the below your query. As we have to calculate Prior Month,Prior Qtr, Prior Year etc.

Measure  =
IF (
    ISFILTERED ( 'Single Column Data'[Major group] ),
    SUMX ( 'Single Column Data', 'Single Column Data'[Amount] ),
    SUMX (
        FILTER ( 'Single Column Data', 'Single Column Data'[Major group] = "Assets" ),
        'Single Column Data'[Amount]
    )
        - SUMX (
            FILTER (
                'Single Column Data',
                'Single Column Data'[Major group] = "Liabilities & Equity"
            ),
            'Single Column Data'[Amount]
        )
)

Thanks

Smita

[ImkeF]

You wouldn't do that in that measure. Instead you can reference it with time intelligence functions (if you have a separate Calendar-Table for this). See this article for more details for example: https://exceleratorbi.com.au/dax-time-intelligence-beginners/

PriorMonth = CALCULATE( [Measure], DATESADD(‘Calendar'[Date], -1, MONTH))
PriorYear = CALCULATE( [Measure], DATESADD(‘Calendar'[Date], -1, Year))
...

[v-cherch-msft · Accepted solution]

Hi @Anonymous

You may try to create a measure to change the total value. Such as:

Measure  =
IF (
    ISFILTERED ( 'Single Column Data'[Major group] ),
    SUMX ( 'Single Column Data', 'Single Column Data'[Amount] ),
    SUMX (
        FILTER ( 'Single Column Data', 'Single Column Data'[Major group] = "Assets" ),
        'Single Column Data'[Amount]
    )
        - SUMX (
            FILTER (
                'Single Column Data',
                'Single Column Data'[Major group] = "Liabilities & Equity"
            ),
            'Single Column Data'[Amount]
        )
)

Regards,

Cherie

[Anonymous]

Hello Cherrie,

     That's marvelous!!! Thank you so much for your help. This really works for me.

Many thanks,

Praveen.