Dax Function to Calculate Variance

[Nanakwame]

Hi Experts,

I have a table in which a created a measure to calculate Margin for each department. 

When i pull in this measure into a matrix table with Month in Rows and Years in column and Margin in values i get the table attached. I will like to add a column that compares 2020 and 2021 margin. Any idea how i can achieve this?

[v-xiaotang]

Hi @Nanakwame 

Have you solved this question with Pragati11's help? If you have solved the question, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.

If you need more help, please provide:

(1) a sample file, you can replace raw data with bogus data to protect your privacy.

(2) or provide some sample data that fully covers your issue/question

(3) give your expected result based on the sample you provide

Kindly note: Please ensure the data in sample is concise and representative.

Thanks.

Best Regards,

Community Support Team _Tang

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

[Pragati11]

HI @Nanakwame ,

You can use VAR.P dax function to calulate variance between 2 columns.

See the reference here:

https://docs.microsoft.com/en-us/dax/varx-p-function-dax

Thanks,

Pragati

[Nanakwame]

Hi @Pragati11 

Will this work if i technically do not have 2 columns in the data? I actually just have 1 which is "margin" however when i create a matrix table and i use "Year" column which breaks the margin down by year, i will like a function that will automatically take the breakdown into effect and compare the margins. 

[DavidAnthony]

Flexa Tables can help address your scenario! Based on its features, Flexa Tables is designed to transform static Power BI tables into dynamic, pivotable tools with built-in variance calculations, making it ideal for comparing metrics like your profit margin across different time periods. Since you want to add a column to compare profit margins between 2020 and 2021 in your matrix (with Months in rows, Years in columns, and Margin in values), Flexa Tables’ ability to handle day-over-day (DoD), month-over-month (MoM), and year-over-year (YoY) comparisons can simplify this.You can leverage its variance calculation feature to create a custom measure that directly compares 2020 and 2021 margins. For example, you could set up a measure to calculate the difference or percentage change between the two years, using its intuitive drag-and-drop functionality to integrate this into your matrix. This eliminates the need for manual exports or complex DAX rewrites, aligning with your goal. While the exact implementation depends on your data structure, Flexa Tables’ design to streamline time-based comparisons suggests it can handle this efficiently. I recommend testing it with your dataset to confirm, but it’s well-suited for this task!

[Pragati11]

Hi @Nanakwame ,

There are few existing threads on the forum for this on how to calculate variance for different years:

https://community.powerbi.com/t5/Desktop/Calculating-Variance-Year-on-Year/m-p/168568

https://community.powerbi.com/t5/Desktop/Calculate-Year-over-Year-Variance-Earlier-Function-Not-Work...

If these don't help, then try sharing some sample data along with the expected result for me to try at my end.

Thanks,

Pragati