How to calculate variance with two row values

[Anonymous]

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?

[v-rzhou-msft]

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

[v-rzhou-msft]

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. 

[amitchandak]

@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