Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
julpol
Regular Visitor

Multiple variance calculation in matrix

Hi everyone

 

I'm doing a report in which I have multiple years as columns and some categories as rows. I would like to see two variance columns at the end, first as a difference between the last year's value and previous year's value, and the second variance would should the %.

 

julpol_0-1724320725793.png

 

I tried creating the first measure but i'm not able to get the value for those two years. Could you please review the DAX code below and advise what I'm missing there? Obviously the filter does not work as intended, but why?

 

If I replace the BLANK() with MaxYear, the system shows correctly 2018

julpol_2-1724321629801.png

 

If I replace the BLANK() with PrevYea, the system shows correctly 2017

If I replace the BLANK() with MaxYearValue, the system shows incrrectly SUM of values from all years, not only the last two.

julpol_3-1724321653647.png

 

Variance $ = 
VAR MaxYear = CALCULATE(MAX('Table'[Year]))
VAR PrevYear = MaxYear-1
VAR MaxYearValue = CALCULATE(SUM('Table'[Value]),FILTER('Table',[Year]=MaxYear))
VAR PreviousYearValue = COALESCE(CALCULATE(SUM('Table'[Value]),'Table'[Year] = PrevYear),0)

RETURN 
IF(
    ISINSCOPE('Table'[Year]) ,
    MaxYearValue - PreviousYearValue,
   BLANK()
)

 

If I add another the second measure into the matrix, both measures are displayed for each year. Why and how do I fix?

julpol_1-1724321544946.png

 

 

Thank you

1 ACCEPTED SOLUTION

Hi @julpol ,

 

Did I solve your problem? Or you can also try turning off the following two options:

vtianyichmsft_0-1724982876520.png

 

Best regards,
Community Support Team_ Scott Chang

 

 

View solution in original post

8 REPLIES 8
bhanu_gautam
Super User
Super User

@julpol ,

First, let's create measures to calculate the values for the maximum year and the previous year.

DAX
MaxYearValue =
VAR MaxYear = CALCULATE(MAX('Table'[Year]))
RETURN CALCULATE(SUM('Table'[Value]), 'Table'[Year] = MaxYear)

 

DAX
PreviousYearValue =
VAR MaxYear = CALCULATE(MAX('Table'[Year]))
VAR PrevYear = MaxYear - 1
RETURN CALCULATE(SUM('Table'[Value]), 'Table'[Year] = PrevYear)

 

Then create a mesure for Variance

 Variance$ =
   VAR MaxYear = CALCULATE(MAX('Table'[Year]))
   VAR PrevYear = MaxYear - 1
   VAR MaxYearValue = CALCULATE(SUM('Table'[Value]), 'Table'[Year] = MaxYear)
   VAR PreviousYearValue = CALCULATE(SUM('Table'[Value]), 'Table'[Year] = PrevYear)
   RETURN
   IF(
       ISINSCOPE('Table'[Year]),
       MaxYearValue - PreviousYearValue,
       BLANK()
   )
 
Then one measure for Variance %
DAX
Variance% =
VAR MaxYear = CALCULATE(MAX('Table'[Year]))
VAR PrevYear = MaxYear - 1
VAR MaxYearValue = CALCULATE(SUM('Table'[Value]), 'Table'[Year] = MaxYear)
VAR PreviousYearValue = CALCULATE(SUM('Table'[Value]), 'Table'[Year] = PrevYear)
RETURN
IF(
ISINSCOPE('Table'[Year]),
DIVIDE(MaxYearValue - PreviousYearValue, PreviousYearValue, 0),
BLANK()
)



Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






Hi @bhanu_gautam 

 

unfortunately, the result is the same, 


1) both variance measures are blank in the matrix

2) when both are used, they are added to every column

 

julpol_0-1724370776989.png

julpol_1-1724370834805.png

 

 

Hi @julpol ,

 

There is no problem with your calculation logic. The problem is because you are putting multiple fields in the Values field of the matrix.For an matrix. multiple fields can be put in the row, which represents the hierarchy, whereas both columns and Values should have only one aggregated field.

 

Hope it helps!

 

Best regards,
Community Support Team_ Scott Chang

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-tianyich-msft ,

 

re 1) - the variance$ has probably a wrong filter as its value is $0? Please refer to first screenshot where only one variance is used in teh Values field, and the column shows $0 across all rows.

 

re 2) - is there a way to achieve this? To have two extra calculated columns on the right, one $ variance and one % variance.

 

Thank you

Hi @julpol ,

 

It will give the same results as the last screenshot of your initial post, I made simple samples and you can check the results below:

vtianyichmsft_0-1724398749891.png

Variance$ = var _t = ADDCOLUMNS('Table',"pre",MAXX(FILTER(ALL('Table'),[Category]=EARLIER([Category])&&[Year]=EARLIER([Year])-1),[Value]))
RETURN MAX('Table'[Value])-MAXX(_t,[pre])

Variance% = var _t = ADDCOLUMNS('Table',"pre",MAXX(FILTER(ALL('Table'),[Category]=EARLIER([Category])&&[Year]=EARLIER([Year])-1),[Value]))
RETURN DIVIDE(MAX('Table'[Value])-MAXX(_t,[pre]),SUM('Table'[Value]))

 

An attachment for your reference. Hope it helps!

 

Best regards,
Community Support Team_ Scott Chang

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-tianyich-msft thank you for your help. Is it possible to change the matrix so it does not show the variance fields as values per each Year column? I would like only the value from the "value" field to be visible per each year and then at the end see two variance columns.

Thanks

Hi @julpol ,

 

Did I solve your problem? Or you can also try turning off the following two options:

vtianyichmsft_0-1724982876520.png

 

Best regards,
Community Support Team_ Scott Chang

 

 

Hi @julpol ,

 

Is this your desired result?

vtianyichmsft_0-1724640634586.png

 

Variance% = var _t = ADDCOLUMNS('Table',"pre",MAXX(FILTER(ALL('Table'),[Category]=EARLIER([Category])&&[Year]=EARLIER([Year])-1),[Value]))
RETURN IF(ISINSCOPE('Table'[Year]),BLANK(),DIVIDE(MAX('Table'[Value])-MAXX(_t,[pre]),SUM('Table'[Value])))

Variance$ = var _t = ADDCOLUMNS('Table',"pre",MAXX(FILTER(ALL('Table'),[Category]=EARLIER([Category])&&[Year]=EARLIER([Year])-1),[Value]))
RETURN IF(ISINSCOPE('Table'[Year]),BLANK(),MAX('Table'[Value])-MAXX(_t,[pre]))

 

 

Hope it helps!

 

Best regards,
Community Support Team_ Scott Chang

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Top Solution Authors