Skip to main content
cancel
Showing results for
Search instead 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

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

If so please try:

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:

You can Pivot your column:

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?

If so please try:

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:

You can Pivot your column:

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

Did I answer your question? Mark my post as a solution!
Appreciate your Kudos

Proud to be a Super User!

Follow me on linkedin

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.

## Helpful resources

Announcements

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors