Adding a New column to Matrix that find the difference between two columns

[Anonymous]

Hello, 

I have this Matrix and I would like to be able to add a third column to the right that shows the difference between the two year columns. The Matrix is just all these values and then year in the columns. I know this is kinda out of the bounds of what a matrix is meant to do but if anyone has suggestions on how to go about this or a work around please let me know. 

[tamerj1 · Accepted solution]

Hi @Anonymous 
You may activate the columns total and rename it "Difference". Use the following measure in the matrix visual values.

ResultMeasure =
IF (
    HASONEVALUE ( 'Table'[Fiscal Year] ),
    [Measure],
    CALCULATE ( [Measure], 'Table'[Fiscal Year] = MAX ( 'Table'[Fiscal Year] ) )
        - CALCULATE ( [Measure], 'Table'[Fiscal Year] = MIN ( 'Table'[Fiscal Year] ) )
)

[Anonymous]

Thanks that worked great! If you have the time can you explain how overriding the column subtotal works. I'm trying to have a better understanding of whats going on here.

[tamerj1]

@Anonymous 

The following usually comes as a surprise to all new Power Bi users. 
The Total in power bi does not actually sum the visible values in the matrix. It is evaluated just as any other cell but in different filter context. In you example the total column is evaluated in the context of ALL ( [fiscal year] ). If the measure is of additive nature, total column will show the sum of all years. However, we can change that and produce the difference for example. By checking IF [fiscal year] HASEONVALUE (such as in f2023 and f2024) we can apply the normal measure while otherwise we can apply the difference between the value of the max year and the value of the min year. As simple as that.