2 Replies Latest reply on May 5, 2018 10:53 AM by Kaul Smith

    LOD Calculation

    Kaul Smith

      Hello,

       

      I am facing a problem in creating an LOD formula. Can anyone please help on this?

       

      I like to created two fields Sales and Profit the conditions would be if marketing is equal to zero then I want Operations value in Sales column and if marketing is not equal to zero then I want Operations value in Profit column.

       

      And their is another condition which I am facing problem is when there are multiple lines for Order Id's for example when Order ID -  100D has marketing values zero and 1250 ( WITH SAME PART NUMBERS) then I want operations values 660 and 2385 in Sales Column instead of 660 in Profit column.

       

      If it is a unique part number then 660 in Profit column is good.

       

      I attached my workbook.

       

       

       

      Final result would be like this   

        • 1. Re: LOD Calculation
          Zhouyi Zhang

          Hi, Kaul

           

          Please find my solution attached.

           

           

          Hope this helps

           

          ZZ

          1 of 1 people found this helpful
          • 2. Re: LOD Calculation
            Kaul Smith

            Thank you very much Zhouyi, this works Perfect!!!

             

            There is one more problem for me if you don't mind can you please help on this?

             

            I attached workbook and made few tabs.

             

            There is a created calculated fields to calculate Profit% and Sales% and want I am trying to display on grand total for Company F1 is total percentage of Marketing, Misc and Other which is 92.12% instead of 97.37% and same thing for Sales like to display 61.10% instead of  79.99%.

             

             

            One more thing there are multiple Company's, Region's and Corporate and I added these fields to the context but when I select multiple company's the percentages are changing for company F1 because the profit and sales percentages formula is calculating from grand total instead of  individual company's total.

             

             

            When you compare two screen shots the percentage values for factors for F1 company  are changing for when I select multiple screen shots. But I want to keep the same for F1 even when I select multiple company's or regions or coporate

             

            So, Finally I am expecting to display like this when I select multiple filters, in below screen shot the profit and sales percentages are calculating using company's total instead of grand total.

             

             

            Note:

             

            this formula works but doesn't display percentage values in Sales column and I like to include % values in sales column but total I want to eliminate Finance, HR and Sales