cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

## Beginner Help - Want to add variance column to table

I am trying to figure out how to add a variance column (difference between 2021 and 2022 for March would be -20) to a table.  Any help would be appreciated.

1 ACCEPTED SOLUTION
Community Support

Hi @mdonawho ,

It seems that you are using a Matrix visual?

Rename the Column Subtotal:

Then apply the measure to the value field:

``````Measure =
IF (
ISINSCOPE ( 'Table (2)'[Year] ),
SUM ( 'Table (2)'[Value] ),
CALCULATE ( SUM ( 'Table (2)'[Value] ), FILTER ( 'Table (2)', [Year] = 2022 ) )
- CALCULATE ( SUM ( 'Table (2)'[Value] ), FILTER ( 'Table (2)', [Year] = 2021 ) )
)
``````

Output:

Another solution:

Output:

Then create a measure like @negi007  mentioned :

``Varience = SUM('Table'[2022]) - SUM('Table'[2021])``

Final output:

Best Regards,

Jianbo Li

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

4 REPLIES 4
Community Support

Hi @mdonawho ,

It seems that you are using a Matrix visual?

Rename the Column Subtotal:

Then apply the measure to the value field:

``````Measure =
IF (
ISINSCOPE ( 'Table (2)'[Year] ),
SUM ( 'Table (2)'[Value] ),
CALCULATE ( SUM ( 'Table (2)'[Value] ), FILTER ( 'Table (2)', [Year] = 2022 ) )
- CALCULATE ( SUM ( 'Table (2)'[Value] ), FILTER ( 'Table (2)', [Year] = 2021 ) )
)
``````

Output:

Another solution:

Output:

Then create a measure like @negi007  mentioned :

``Varience = SUM('Table'[2022]) - SUM('Table'[2021])``

Final output:

Best Regards,

Jianbo Li

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

Community Champion

@mdonawho in this case you can create below measures

2021_value = CALCULATE(SUM(sales[value]),YEAR=2021)

2022_value = CALCULATE(SUM(sales[value]),YEAR=2022)

variance = [2022_value]-[2021_value]

you can then use these measures in your matrix to view value for each of the measure

Proud to be a Super User!

Super User

@mdonawho , Create MOYM measure and use that in column total using isinscope

measure = if(isinscope(Table[Year]), [MTD], [MOYM])

Measure using time intelligence and date table

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
Previous year Month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth(dateadd('Date'[Date],-11,MONTH)))
last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))

MOYN =[MTD Sales] - [last year MTD Sales]

Frequent Visitor

Thank you for your reply.  I am not understanging the steps you are directing me to take.

Announcements

#### Power BI Monthly Update - November 2023

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

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors