how can i add variance in a two layered matrix table

[Tun]

Hi all,

i have a two layered matrix table and i want to add variance in current month, ytd and ye. 
So for example, in the current month, i would like to add variance for actual - budget, actual-forecast, ytd would be actual - budget. I have tried several methods, but was not successful. Any help?

[v-ssriganesh]

Hello @Tun, 

Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

Thank you for being part of the Microsoft Fabric Community.

[v-ssriganesh]

Hello @Tun,

Hope everything’s going great with you. Just checking in has the issue been resolved or are you still running into problems? Sharing an update can really help others facing the same thing.

Thank you.

[v-ssriganesh]

Hello @Tun,

We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.

Thank you

[v-ssriganesh · Accepted solution]

Hello @Tun,

I have reproduced your scenario in Power BI Desktop and was able to achieve the expected output as per your requirement. I created variance measures for:

• Current Month: Actual – Budget, Actual – Forecast
• YTD: Actual – Budget
• YE: Actual – Budget

This gave me the correct results in the matrix visual:

For your reference, I am attaching a sample .pbix file that demonstrates the full solution. You can review and adapt the same logic to your own model.

Best regards,
Ganesh Singamshetty.

[Shahid12523]

Create variance measures in DAX, e.g.:

Var_CM_Act_Budget = [Actual_CM] - [Budget_CM]
Var_CM_Act_Forecast = [Actual_CM] - [Forecast_CM]
Var_YTD_Act_Budget = [Actual_YTD] - [Budget_YTD]
Var_YE_Act_Budget = [Actual_YE] - [Budget_YE]

Add these measures into your matrix right after Actual (under Current Month, YTD, YE).

(Better) Use a Calculation Group in Tabular Editor → add Budget, Forecast, Actual, Variance as calc items → Power BI auto-layers them neatly.

[Aburar_123]

Hi @Tun ,

you can write a measure as below,

Variance = if(max('Table'[category])="Current Month", sum(actual)-sum(forecast),

if(max('Table'[category])="YTD", sum(actual)-sum(budget)

))

Thanks.

[GeraldGEmerick]

@Tun Are the columns in the second row measures or columns? If they are measures, you should be able to construct a measure like the following and add it as a value.

Variance = [actual] - [budget]