cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anno2019
Helper III
Helper III

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

@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


Regards
Zubair

Please try my custom visuals

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

@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 ) )
Zubair_Muhammad
Community Champion
Community Champion

@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 )
    )

Regards
Zubair

Please try my custom visuals

Hi @Zubair_Muhammad / @Anonymous ,

 

This is a great idea to display the variance in the same row of the measure column and I need your assistance to display the "Total" value as well. My requirement is like below table. I am trying to add total value in dax measure but I am unable to acheive the results because we have displayed the variance instead of total value. kindly help me!!

 


Matrix table.PNG

DAX Reference : (to display the variance but I need total value as well)

IF (
        HASONEFILTER (Table1[Product]),
        SUM (Sales), Variance(YOY)
)

 

Hi Zubair

I will try this...but stupid question, how will i display this as the total in the 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


Regards
Zubair

Please try my custom visuals

View solution in original post

Anonymous
Not applicable

@Anno2019  - 

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

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.