Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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

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...
  • tamerj1's avatar
    tamerj1
    3 years ago

    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's avatar
Anonymous
Not applicable
3 years ago

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's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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.