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. 

[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