DAX formula Help

[VANI_YN_80]

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

[Samarth_18]

Hi @VANI_YN_80 ,

Sorry to say, you have attached your local desktop url for raw data. Could you please upload your raw data file in sharepoint or one drive and share it so that other can access it.

or you could share some sample data with expected output snapshot this would also help.

[VANI_YN_80]

Hi @Samarth_18 

thanks for the reply.

Below is the one drive link. Please check if you can access it. 

https://siemensapc-my.sharepoint.com/personal/vani_narasimhamurthy_siemens_com/_layouts/15/onedrive.... 

Regards

Vani 

[Samarth_18]

Hi @VANI_YN_80 ,

Still its not accessible. You can just paste small set of data here with expected output, this will also help.

Thanks,

Samarth

[VANI_YN_80]

Hi @Samarth_18 ,

Here is my sample data set.

Location ID : STORT	Fiscal year : GJAHR	Fiscal Period : FISCPER	Material Number : MATNR	Order Vendor ID : LIFNR	Purchase order currency : BWAER	Gross value in local currency 2020	Gross value in local currency 2021	Quantity posted in 2020	Quantity posted in 2021
43	2020	2020003	000000000001220508	A1082278	EUR	2,860.00000		285.64299
43	2020	2020003	000000000001220508	A1082278	EUR	722.50000		72.15999
43	2020	2020012	000000000001220508	A1082278	EUR	3,546.59999		385.01900
43	2020	2020003	000000000001220508	A1082278	EUR	-228.80000		0.00000
43	2020	2020003	000000000001220508	A1082278	EUR	-57.79999		0.00000
43	2020	2020003	000000000001220508	A1082278	EUR	-228.80000		0.00000
43	2020	2020003	000000000001220508	A1082278	EUR	-57.79999		0.00000
43	2020	2020003	000000000001220508	A1082278	EUR	228.80000		0.00000
43	2020	2020003	000000000001220508	A1082278	EUR	57.79999		0.00000
43	2021	2021006	000000000001220508	0010872860	EUR		629.38999		3.99500
43	2021	2021008	000000000001220508	0010872860	EUR		639.23000		4.05700
43	2021	2021008	000000000001220508	0010872860	EUR		0.01000		0.00000

Here the output expected.

total qty 2020	gross value total 2020	APP 2020	PPC  (APP 2020 - APP 2021 *total qty 2021
742.82	6842.5
		9.211518268
total qty 2021	gross value total 2021	APP 2021
8.05	1268.63	157.5937888	-1259.42

Regards

Vani

[v-jingzhang · Accepted solution]

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])

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as a solution to help other members find it.

[Anonymous]

hi @VANI_YN_80 ,

Unable to access the file. 

Thanks!