9 Replies Latest reply on Jul 31, 2018 8:04 AM by Joe Oppelt

    Percentage calculation from 2 worksheets

    Vandana Samtani

      Hi All,

       

      I have 2 work sheets on my dashboard and the user wants to calculate the percentage of grand totals between both sheets either in a separate sheet or in worksheet 2.

       

      for e.g. % Profit between sheet 1 and sheet 2  for title 1= 110803/613886

                  % Sales between sheet 1 and sheet 2  for title 1= 90203/475826

                  % Profit between sheet 1 and sheet 2  for both titles =(110803+4524)/(613886+47696)

                  % Sales between sheet 1 and sheet 2  for both titles=(90203+3132)/(475826+39483)

       

      Any suggestions will be very helpful.

       

      Thanks,

      Vandana

       

        • 1. Re: Percentage calculation from 2 worksheets
          Joe Oppelt

          You'll have to generate both sets of numbers in one sheet.

           

          I know your example is just a subset of data.  Right now it shows one chunk of locations in one sheet, and the rest of the locations in the other.  Is this how it would be in real data?  Or would the user be able to select any bunch on sheet 1, and any other bunch on sheet 2, not mutually exclusive of each other...

           

          What I'm driving at is this:  if you have mutually exclusive sets, then it won't be hard to get the grand totals of both on one sheet.  Use FIXED LODs to get the total sums, and then the grand total from sheet 2 would be the grand total from all data, minus the grand total from sheet 1.

           

          So the exercise you need to do will  depend on how the locations on these sheets get selected.

          • 2. Re: Percentage calculation from 2 worksheets
            Vandana Samtani

            Hi Joe,

             

            Thanks for your inputs.

            The user will select multiple locations depending upon his details in analysis on sheet 1 and same with Sheet 2.

             

            can you please send me a sample of the logic?

             

            Thanks,

            Vandana

            • 3. Re: Percentage calculation from 2 worksheets
              Joe Oppelt

              I don't see a good way to do what you are looking to do.

              • 4. Re: Percentage calculation from 2 worksheets
                Vandana Samtani

                Quick Question - Since both data set (Sheet 1 and Sheet 2) is from the same source. is there a way i can use one sheet to calculate the percentage and then keep it in different buckets?

                 

                Any suggestion will be very helpful.

                 

                Thanks,

                Vandana

                • 5. Re: Percentage calculation from 2 worksheets
                  Joe Oppelt

                  Everything is done within the context of individual sheets.  We don't have the mechanism to store measure values and pass them to another sheet.  Those measures have to be computed in the sheet where they will be used.

                   

                  A way to do this would be to store the selected locations from the first sheet in individual parameters.  However, parameters can store only single values.  If your user is going to select only one value, then the question is easy.  But from what I understand, your user can select any number of locations, so you would have to have as many parameters as the user could possibly select.  You can do this, but it would be very cumbersome.

                  • 6. Re: Percentage calculation from 2 worksheets
                    Vandana Samtani

                    Hi Joe,

                     

                    The users are OK viewing all the results in one sheet and at one time they will select 2 locations only for this analysis.

                     

                    Can you please share with me as how i can use the parameters?

                     

                    Thanks,

                    Vandana

                    • 7. Re: Percentage calculation from 2 worksheets
                      Joe Oppelt

                      If you are just going to have two values, it's a lot easier.  In the attached I created two params, and set a calc (which I called [Selection]) based on them.  Then I put [Selection] on filters so that I only pull up the two selected locations.  (I excluded null, essentially.)

                       

                      Now you could even have two sheets, one with Location 1, and one with Location 2.  On either sheet you can still get access to the data for the other with FIXED LODs if you wanted.

                       

                      We don't have to do any tedious formatting of x-many parameters (one for each possible location.)  Just let the user select location 1 from the first parameter, and location 2 from the second parameter.

                      • 8. Re: Percentage calculation from 2 worksheets
                        Vandana Samtani

                        Thanks Joe!

                         

                        Can you also show me how the percentage calculation will work using LOD?

                         

                        Can I still do 110,803/613886?

                         

                        Thanks,

                        Vandana

                        • 9. Re: Percentage calculation from 2 worksheets
                          Joe Oppelt

                          Vandana -- You asked the same question you originally asked at the beginning.

                           

                          What I uploaded in my previous workbook was an example where your user will select two locations to compare.  The question you just asked is looking for a random set of locations in analysis 1 compared to a random set of locations in analysis 2.  That's going to take a totally different approach from what you asked yesterday:  "The users are OK viewing all the results in one sheet and at one time they will select 2 locations only for this analysis."

                           

                          If your users are going to select just to locations to compare, then "Sheet 2(2)" in the workbook I provided yesterday shows how to do it.

                           

                          In the workbook I attached to this reply, on sheet 2(3) I have added a new calc that uses LOD to grab the profit value for Selection 2.  You will see that even under Selection 1 on that sheet, I'm displaying the profits for selection 2.  And further, on sheet 2(4), I have filtered to display only Selection 1, but I can still display the profits for Selection 2 with that LOD calc.

                           

                          So now you can do:

                           

                          SUM(Profits) / SUM (Profits for Selection 2)

                           

                          and get your percentage -- but only for comparing the two selections.

                          1 of 1 people found this helpful