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

Anonymous
Not applicable

## How to calculate variance with two row values

Hi All,

Im new to powerbi.

I have requirement like calculating the variance from two rows ,it should be something like this

 IT Finance HR Previous Month 706 26 230 Current Month 712 27 227 Variance +0.85% +3.85% -1.32%

Is this can be done?

3 REPLIES 3
Community Support

Hi @Anonymous

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

Community Support

Hi @Anonymous

In your sample I think you want to build a matrix by adding RowType contains "Previous Month","Current Month" and "Variance" in  Row Fields and add WorkType contains "IT, Finance" and "HR" in Column Fields. Then add values in Value Fields.

In Power BI you can calculate your results by columns instead of rows. And Power BI doesn't support multiple types in one column.

If your sample is as below, please try my way to transform your table in Power Query Editor.

Select columns except RowType and unpivot them.

Then pivot RowType and Value, then add a custom column.

New Table:

Result is as below.

Best Regards,

Rico Zhou

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

Super User

@Anonymous , is this your source data? This seems like a matrix display with Measure on row using"Show on row"

Nothing on row, department on column.

And three measure this month , last month and diff

Example of such measure with time intelligence

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
next month Sales = CALCULATE(SUM(Sales[Sales Amount]),nextmonth('Date'[Date]))
this month =MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value = CALCULATE(sum('Table'[total hours value]),previousmonth('Date'[Date]))

diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

there is no row level calculation in power bi

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.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors