Forum Discussion
Variance Column
I have a matrix that looks like this
company 2022 2023 2024
orders 2342 1234 5678
sales 2342 1234 5678
income 2342 1234 5678
I want to add a column that is year by year variance, so it should look like this
company 2022 2023 2024 Variance
orders 2342 1234 5678 x
sales 2342 1234 5678 y
income 2342 1234 5678 z
I want the variance column to be filled in with the orders vs last year, sales vs last year, and income vs last year. How would I be able to do this?
Give this measure a try
Yearly Variance = VAR CurrentYear = SELECTEDVALUE('Date'[Year]) VAR PreviousYear = CurrentYear - 1 VAR Metric = SELECTEDVALUE('Metrics'[Metric]) RETURN SWITCH( TRUE(), Metric = "Orders", [Orders], Metric = "Sales", [Sales], Metric = "Income", [Income] ) - CALCULATE( SWITCH( TRUE(), Metric = "Orders", [Orders], Metric = "Sales", [Sales], Metric = "Income", [Income] ), 'Date'[Year] = PreviousYear )
4 Replies
Give this measure a try
Yearly Variance = VAR CurrentYear = SELECTEDVALUE('Date'[Year]) VAR PreviousYear = CurrentYear - 1 VAR Metric = SELECTEDVALUE('Metrics'[Metric]) RETURN SWITCH( TRUE(), Metric = "Orders", [Orders], Metric = "Sales", [Sales], Metric = "Income", [Income] ) - CALCULATE( SWITCH( TRUE(), Metric = "Orders", [Orders], Metric = "Sales", [Sales], Metric = "Income", [Income] ), 'Date'[Year] = PreviousYear )Anonymous ,
Which value are you expecting in place of x,y,z? Please provide any logic or formula.
Anonymous ,
Correct if me if I'm wrong,
2024[orders]-2023[orders] likewise for others.
