12 Replies Latest reply on Aug 23, 2016 3:54 AM by Steve Taylor

    % Calculations

    Eshwar Prasad

      Hi Team,

       

      i have view like below..

       

      I each category, in region i need to show %. when i am using table calculations it is considering all regions sales amount as denominator and giving %.

       

      But i need to show the % of sales against each Region. means across Category what is the total international sales amount would the denominator.

      it should be like International = 3,161,509/21,482,525

      But it is considering

      International = 3,161,509/30,434,456.

       

        • 1. Re: % Calculations
          Steve Taylor

          Hello Eshwar

           

          If you change your compute using setting for the table calculation to "Category" that will show what % of sales each Category contributes to each region.

           

          Is that the answer you were looking for?

           

          Steve

          • 2. Re: % Calculations
            Kajal Chandani

            Hi Eshwar,
            Please find the answer in the below attached workbook.

             

             

            It's simple here. You can just click on Compute Using "Pane Down".
            This way it will only consider International = 3,161,509/21,482,525,
            denominator will be the sum of values in each Category.

             

             

             

             

             

            forum1.PNG

            1 of 1 people found this helpful
            • 3. Re: % Calculations
              Eshwar Prasad

              No Steve. I tried will all options in the table calculations.

               

              its not giving correct result. It is taking all regions sales as denominator and giving %.

               

              but Denominator should be of total sum of each region respectively across the categories.

              • 4. Re: % Calculations
                Steve Taylor

                Ok, looking at a simpler version (so it all fits on one screen), in the image below:

                 

                 

                What is the numerator and the denominator you require for the first row?

                 

                Delivery Truck, International Sales / SUM(All International Sales) ?

                 

                if that's wrong could you describe using the combinations above?

                 

                Steve

                • 5. Re: % Calculations
                  Eshwar Prasad

                  Delivery Truck, International Sales / SUM(All International Sales) 

                   

                  This is Correct Steve. this is what i am expecting..

                  • 6. Re: % Calculations
                    Steve Taylor

                    Ok, so in the example shown, we're looking at $8,888,015 / $21,482,525 = 41.37%

                     

                    Changing the table calc to compute using Ship Mode gets that result:

                     

                     

                    This is now showing for each region, what % of sales used each ship mode, for International 41.37% was Delivery truck, 8% was Express Air and 50.63% was Regular Air.

                     

                    Is that what you originally wanted? If it is but this doesn't work for the actual data you're using, maybe there are other issues to consider?

                     

                    Steve

                    • 7. Re: % Calculations
                      Eshwar Prasad

                      Yes Steve. this is the what i was asking for. how did you get this. Can you explain.

                       

                      I have exact requirement. why it wont work for my data?

                      • 8. Re: % Calculations
                        Steve Taylor

                        I got to this answer by setting the table calculation to compute using Ship Mode (or whatever dimension is first on the rows shelf).

                         

                        If you go into the "Edit table calculation" window then in the summarise the values from menu choose advanced, the setup of fields looks like this:

                         

                         

                        The Partitioning & Addressing sections are better though of as being "per" (partitioning) and "for every" (addressing). So our example would be

                        "Show me the % of total sales per region for every ship mode"

                         

                        If you have more than two dimensions in your real data you need to move the fields around in this window until you reach the correct combination of "per" and "for every".

                         

                        Steve

                        • 9. Re: % Calculations
                          Eshwar Prasad

                          I did in the same Steve before posted this..

                           

                          But i am getting more than 100% , if i am summing it up in my actual Dashboard.

                           

                          am i missing anything ??

                          why it is not working with actual data?

                          • 10. Re: % Calculations
                            Steve Taylor

                            Sorry it's not working, I can imagine the frustration!

                             

                            Without seeing exactly what's going on with your actual data it's difficult to suggest where the error is coming from. Could you create an anonymised sample of your data and recreate the problem in a workbook you could share on the forum?

                             

                            Steve

                            • 11. Re: % Calculations
                              Eshwar Prasad

                              Thank you Steve for your support. I got the answer.

                               

                              i tried in different way like below..

                               

                              SUM( { FIXED [ShipMode],[Region]: (sum(Sales))} ) / WINDOW_SUM(sum(sales)).

                              • 12. Re: % Calculations
                                Steve Taylor

                                Glad you found a solution.

                                 

                                Steve