Forum Discussion

VANI_YN_80's avatar
VANI_YN_80
Regular Visitor
5 years ago
Solved

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 formu...
Need Help
  • v-jingzhang's avatar
    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])

     

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

Samarth_18's avatar
Samarth_18
Community Champion
5 years ago

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's avatar
VANI_YN_80
Regular Visitor
5 years ago

Hi Samarth_18 ,

 

Here is my sample data set.

 

Location ID : STORTFiscal year : GJAHRFiscal Period : FISCPERMaterial Number : MATNROrder Vendor ID : LIFNRPurchase order currency : BWAERGross value in local currency 2020Gross value in local currency 2021Quantity posted in 2020Quantity posted in 2021
4320202020003000000000001220508A1082278EUR2,860.00000 285.64299 
4320202020003000000000001220508A1082278EUR722.50000 72.15999 
4320202020012000000000001220508A1082278EUR3,546.59999 385.01900 
4320202020003000000000001220508A1082278EUR-228.80000 0.00000 
4320202020003000000000001220508A1082278EUR-57.79999 0.00000 
4320202020003000000000001220508A1082278EUR-228.80000 0.00000 
4320202020003000000000001220508A1082278EUR-57.79999 0.00000 
4320202020003000000000001220508A1082278EUR228.80000 0.00000 
4320202020003000000000001220508A1082278EUR57.79999 0.00000 
43202120210060000000000012205080010872860EUR 629.38999 3.99500
43202120210080000000000012205080010872860EUR 639.23000 4.05700
43202120210080000000000012205080010872860EUR 0.01000 0.00000

Here the output expected.

total qty 2020gross value total 2020APP 2020PPC  (APP 2020 - APP 2021 *total qty 2021
742.826842.5  
  9.211518268 
    
    
    
total qty 2021gross value total 2021APP 2021 
8.051268.63157.5937888-1259.42
    

 

Regards

Vani

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    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])

     

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