cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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?

1 ACCEPTED SOLUTION
Super User

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 4
Super User

Which value are you expecting in place of x,y,z? Please provide any logic or formula.

Proud to be a Super User!

Frequent Visitor

variance of orders from 2023 to 2024 in x, variance of sales from 2023 to 2024 in y, variance of income from 2023 to 2024 in z

Super User

Correct if me if I'm wrong,

2024[orders]-2023[orders] likewise for others.

Proud to be a Super User!

Super User

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors