Variance Calculation between Two Columns on Matrix - Best method of doing this?

[getsmithed]

Hi everyone,

This is something I've struggled with on multiple dashboards I've built for clients. In the screenshot above for example, I'm trying to find the variance between 2025 Revenue and 2024 Revenue. Normally what I do is create a visual calculation that contains two variables such as: 

CY = CALCULATE(SUM([Revenue]), Year = 2025)

LY = CALCULATE(SUM([Revenue]), Year = 2024) 

RETURN 

CY - LY 

Then I would have three different 'Variance' columns showing for each year and the total. I would hide two of them and use one to display. If I don't do it this way, I normally create a separate matrix next to the 'main' matrix and use a measure for variance and hide the row headers to make it seem as if its connected the same 'main' matrix.

It seems like such a simple task, but requires multiple 'formatting' steps I find unneccesary, so I'm wondering if anyone has a better way of doing this than I do. Any ideas at all I would be interested to hear! Thanks in advance

[v-echaithra]

Hi @getsmithed ,

As we haven’t heard back from you and did not find any sample data to work with , we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra.

[v-echaithra]

Hi @getsmithed ,

We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra.

[getsmithed]

Hi Chaithra, 
Your 'solution' is something I've already put in the description for my question. Not sure why you thought that would be considered a solution after reading my question. I asked for alternative methods to doing this.

[v-echaithra]

Hi @getsmithed ,

Apologies for the earlier response—I now realize you were specifically asking for alternative approaches, not the one you'd already mentioned. I would request you to please share a small sample of your dataset and the expected output.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

[v-echaithra]

Hi @getsmithed ,

Thank you for reaching out to Microsoft Commuity.
Inorder to acheive the variance column, you can create a measure using the below DAX and add it to your matrix visual.

Revenue Variance =

VAR Revenue2025 = CALCULATE([Revenue], 'Date'[FiscalYear] = 2025)

VAR Revenue2024 = CALCULATE([Revenue], 'Date'[FiscalYear] = 2024)

RETURN

Revenue2025 - Revenue2024

If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

Thanks,
Chaithra E.

[mark_endicott]

@getsmithed - You could always use a table visuals and create some very specific measures. It'll be quicker and use less formatting, but, it will not be dynamic. 

Although saying that, the way you have to write the visual calculation isn't either.

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!