DAX formula Help

Hi Everyone,

I have this Raw data and i want to calculate a new column called as PPC. The Formula for PPC is APP 2021 - APP2020 *Quantity posted 2021.

could yop please help me with the formula ? This is my Raw Data.

C:\Users\Z002KNWX\Desktop\Purchase Variance analysis\New

regards

Vani

Need Help

v-jingzhang

5 years ago

Hi VANI_YN_80

Based on your data, you can create measures to get the result.

total qty 2020 = SUM('Table'[Quantity posted in 2020])

gross value total 2020 = SUM('Table'[Gross value in local currency 2020])

APP 2020 = DIVIDE([gross value total 2020],[total qty 2020])

total qty 2021 = SUM('Table'[Quantity posted in 2021])

gross value total 2021 = SUM('Table'[Gross value in local currency 2021])

APP 2021 = DIVIDE([gross value total 2021],[total qty 2021])

PPC = [APP 2020] - [APP 2021] * [total qty 2021]

If you want to get the result with a single measure instead of above meaures, you can use below one.

PPC 2 = DIVIDE(SUM('Table'[Gross value in local currency 2020]),SUM('Table'[Quantity posted in 2020])) - DIVIDE(SUM('Table'[Gross value in local currency 2021]),SUM('Table'[Quantity posted in 2021])) * SUM('Table'[Quantity posted in 2021])