- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

# How to create variance field and variance %

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-29-2017 04:55 AM

Hello all,

i'm beginner and just want to know how i can create the **variance** of column** 1** and **2** and also **the variance %**

the numbers 1 until 5 represent columname MONTH and the products represent columnname PRODUCT.

Kind regards,

Rega

Solved! Go to Solution.

Accepted Solutions

## Re: How to create variance field and variance %

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-29-2017 08:20 PM

Hi @regasanyoto,

You can create measures below:

Variance = CALCULATE(SUM(Table1[Amount]),FILTER(Table1,'Table1'[Month]=1))-CALCULATE(SUM(Table1[Amount]),FILTER(Table1,'Table1'[Month]=2))

Variance % = DIVIDE('Table1'[Variance],CALCULATE(SUM(Table1[Amount]),FILTER(ALL(Table1),'Table1'[Month]=1)))

By the way, I would suggest you display those two measures in card visuals. As you use matrix visual to display data originally, if we add two measures to this matrix, two measures values will display under each column group, means under 1, 2,...,5, each will have three columns. It will make the matrix messy, as you just want to return variance between the 1 and 2.

Best Regards,

Qiuyun Yu

If this post

**helps**, then please consider

**to help the other members find it more quickly.**

*Accept it as the solution*All Replies

## Re: How to create variance field and variance %

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-29-2017 12:49 PM

Do you mean like:

Variance = [1] - [2] Variance% = ([1] - [2]) / [1]

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

Proud to be a Datanaut!

## Re: How to create variance field and variance %

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-29-2017 08:20 PM

Hi @regasanyoto,

You can create measures below:

Variance = CALCULATE(SUM(Table1[Amount]),FILTER(Table1,'Table1'[Month]=1))-CALCULATE(SUM(Table1[Amount]),FILTER(Table1,'Table1'[Month]=2))

Variance % = DIVIDE('Table1'[Variance],CALCULATE(SUM(Table1[Amount]),FILTER(ALL(Table1),'Table1'[Month]=1)))

By the way, I would suggest you display those two measures in card visuals. As you use matrix visual to display data originally, if we add two measures to this matrix, two measures values will display under each column group, means under 1, 2,...,5, each will have three columns. It will make the matrix messy, as you just want to return variance between the 1 and 2.

Best Regards,

Qiuyun Yu

If this post

**helps**, then please consider

**to help the other members find it more quickly.**

*Accept it as the solution*## Re: How to create variance field and variance %

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-30-2017 01:46 AM

## Re: How to create variance field and variance %

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-30-2017 01:51 AM

HI @v-qiuyu-msft,

Thanks for your advise. But what if want to have measures which calculate the variance and variance % between month 2 and 3 or 4 and 5 ? So to have a flexible measurements?

## Re: How to create variance field and variance %

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-13-2017 08:47 AM

## Re: How to create variance field and variance %

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-04-2017 07:29 AM

How did you get the Table reference to work? I can't find table names or anything similar to that using the current PowerBI Desktop.