cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anno2019 Frequent Visitor
Frequent Visitor

How to show the variance between two year rows in a matrix

Hi Power BI Guru's

 

Need help, please.

Below "Total" is the total amount calculating sum of 2018 & 2019.  I need the "Total" Row to show the difference by way of subtracting 2019 and 2018...

I already have a column for Variance, but cannot seem to get it to look exactly as below.

Any Takers?

Matrix Table_How to subtract two rows.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: How to show the variance between two year rows in a matrix

@Anno2019 

 

It will automatically appear in the total as the difference between highest selected year's value and lowest selected year's value

Please see the attached file for clarity

6 REPLIES 6
Super User
Super User

Re: How to show the variance between two year rows in a matrix

@Anno2019 

 

Try this MEASURE.

 

Measure =
VAR MaxYear =
    MAXX ( ALLSELECTED ( Table1[Economic Year SAP] ), [Economic Year SAP] )
VAR MinYear =
    MINX ( ALLSELECTED ( Table1[Economic Year SAP] ), [Economic Year SAP] )
RETURN
    IF (
        HASONEFILTER ( Table1[Economic Year SAP] ),
        SUM ( Table1[Value] ),
        CALCULATE ( SUM ( Table1[Value] ), Table1[Economic Year SAP] = MaxYear )
            - CALCULATE ( SUM ( Table1[Value] ), Table1[Economic Year SAP] = MinYear )
    )
natelpeterson Senior Member
Senior Member

Re: How to show the variance between two year rows in a matrix

@Anno2019 -

Try something like this for your Measure:

 

My Diff Measure =
CALCULATE ( [some value], FILTER ( 'Date', Year = 2019 ) )
    - CALCULATE ( [some value], FILTER ( 'Date', Year = 2018 ) )
Anno2019 Frequent Visitor
Frequent Visitor

Re: How to show the variance between two year rows in a matrix

Hi Zubair

I will try this...but stupid question, how will i display this as the total in the matrix?

Super User
Super User

Re: How to show the variance between two year rows in a matrix

@Anno2019 

 

It will automatically appear in the total as the difference between highest selected year's value and lowest selected year's value

Please see the attached file for clarity

Anno2019 Frequent Visitor
Frequent Visitor

Re: How to show the variance between two year rows in a matrix

Zubair, you are my new best friend...thank you kindly...will follow your instructions....

natelpeterson Senior Member
Senior Member

Re: How to show the variance between two year rows in a matrix

@Anno2019  - 

@Zubair_Muhammad  has a more thorough solution - my measure will only work for the scenario you specified, specifically 2018 and 2019.