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 ,

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

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.aspx?csf=1&web=1&e=7qBHh0&cid=bde35fc9%2D6f03%2D413b%2Da578%2D73b0cb9de730&FolderCTID=0x01200002DEF5BABF735948A3DA6F1D671A7176&id=%2Fpersonal%2Fvani%5Fnarasimhamurthy%5Fsiemens%5Fcom%2FDocuments%2FUserData%2FZ002KNWX%2FPOWER%20BI 

 

Regards

Vani 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    hi VANI_YN_80 ,

     

    Unable to access the file. 

     

    Thanks!

  • 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.