12 Replies Latest reply on Feb 26, 2016 11:06 AM by Romil Shah

    Calculate % of total on a Column with AGG Measure

    Romil Shah

      I am trying to calculate % of total on a AGG column, but unable to do so.

       

      Basically, I have 4 Columns

       

      Column1: 2015 Notional Value (Calculated Field named "2015 Notional")

      Column2: 2015 Trading Days (Calculated Field named "2015 Trading Days")

      Column3: 2015 ADN (Avg. Daily Notional)  (Column1 divided by Column2)

      Column4: ADN as a % of Total

       

      For third column, I just added below calculation to Measure Values Card and pressed "Ctrl+Enter"

       

      Sum[2015 Notional]/Sum[2015 Trading Days] (I tried creating a new calculated field for 3rd column, but it return expected results i.e. column 3=column1/column2)

       

      Above formula is being displayed as "AGG[Sum 2015 Notional].....". I dont see complete formula. Although the results I get are as per the expectations

       

      Now, I want to create a fourth column which is a % Share of Total.

       

      I copy exact same forumula as third column and then right click->Add Calculation->% of Total->Table (down). But I do not get right results.

       

      Can someone please help??

       

      I want something like below....(I am getting first 3 columns right, but % share values I am getting for 4th column is incorrect)

       

      I cannot share actual data, since it is confidential...

       

      20001020012.90323
      50005100064.51613
      3000201509.677419
      400020200

      12.90323

        • 1. Re: Calculate % of total on a Column with AGG Measure
          pooja.gandhi

          Hi Romil,

           

          What is the formula in the 1st and 2nd calculated fields? Also, why do you say 4th column is wrong? What are your expected results for the 4th column? If you are creating a duplicate of the 3rd column and doing a percent of total, for the 1st value I would expect to see 200/1550 = 12.90 which is what you are getting. So I am not sure, what else would you expect to see? Unless, you are missing adding a specific detail about your dataset here in your example.

          • 2. Re: Calculate % of total on a Column with AGG Measure
            YungHee Choi

            Hi. Romil.

             

            You maybe misunderstood dimension vs. measure, discrete vs. continuous and aggreation value.

            I made a sample data set and crosstab sheet for you. Please see a attached file.

            I hope it would be a great help to your problem!

            • 3. Re: Calculate % of total on a Column with AGG Measure
              Romil Shah

              Hi Pooja,

               

              Thanks for your response. Below is the output of tableau report I created.

              Capture.PNG

               

              I followed below steps for Column1 in above report.

               

              Step1: I created below calculated field named "Last 90 Days Notional" which would accumulate only those "Total Value USD" which correspond to last 90 days.

               

              Below is the code for calculated field:-

               

              if DATEDIFF('day',[Date],TODAY()-DATEPART('weekday',TODAY())-1)<90 then [Total Value Usd] else 0 END

               

              Step 2: Then, I created a "calculation" in Measure Values card, formula for which is:

               

              Sum(Last 90 Days Notional)

              Capture2.PNG

               

              Similarly, I repeated Step 1 and Step 2 for Column2 (trading days in last 90 cal days) in the tableau report.

               

              I created calculated field named "Last 90 Days Trading Days" and added a similar code.

               

              I then created a "new calculation" in Measure Values formula for which is:

               

              Sum(Last 90 Days Trading Days)

               

              I tried creating a calculated field for Column3 also, but it didn't return expected results. So I simply added a "new calculation" to Measure Values card formula for which is:

               

              Sum(Last 90 Days Notional) / Sum(Last 90 Days Trading Days)

               

              Everything is as per the expectations till column3 (you can cross check the calculations in tableau report as well). Only problem I am facing is for Column4.

               

              I tried copying calculation for Column3 in Measure Values card and then added Table Calculation to it in order to see % Value of Total.

               

              Column4 Formula Generated by Table Calculation: (highlighted in the second screenshot)

               

              SUM([Last 90 Days Notional])/SUM([Last 90 Days Trading Days]) / TOTAL(SUM([Last 90 Days Notional])/SUM([Last 90 Days Trading Days]))

               

              Formula seems correct. But results are incorrect.

               

              Thanks in advance.

               

              Regards,

              Romil Shah

              BI Intern

              CME Group

              • 4. Re: Calculate % of total on a Column with AGG Measure
                Romil Shah

                Thanks I read your solution. As per your suggestion, I need to convert all measures to dimensions.

                 

                But the problem is I am calculating all 4 Columns. Hence, I would have to add table calculation (% of total) on a Measure.

                 

                It has worked for me in the past. But I guess, I am facing issues because of "AGG" measure. Column3 is AGG a measure computed from Column 1&2.

                 

                Could you please check my reply to Pooja. I have tried to explain in the depth the issue I am facing with Column4.

                 

                 

                Regards,

                Romil Shah

                BI Intern

                CME Group

                • 5. Re: Calculate % of total on a Column with AGG Measure
                  pooja.gandhi

                  Hi Romil,

                   

                  Thanks for such a detailed explanation but I have to admit, I don't fully understand whats going on with the formulas because while using table calcs a whole lot depends on how pills are placed on the view and how partitioning/addressing is set. And this is too much information for me to create a dummy workbook. I recommend attaching your workbook so someone can help.

                   

                  Also, which version of Tableau are you using?

                   

                  Thanks,

                  Pooja.

                  • 6. Re: Calculate % of total on a Column with AGG Measure
                    Romil Shah

                    Hey Thanks Pooja for being so much prompt in replying. And yes I can understand info I shared was too much, but I didn't want to miss anything.

                     

                    I am an Intern so need to which if I can share actual data with you. I can create dummy transactional data, but it would take some time.

                     

                    Tableau version I am using is 9.2

                     

                    Regards,

                    Romil

                    • 7. Re: Calculate % of total on a Column with AGG Measure
                      Romil Shah

                      Please find attached dummy tableau report and dummy underlying excel transactional data.

                       

                      Please let me know if you need anything else...

                       

                      Again, thanks for all the help.

                       

                      Regards,

                      Romil

                      • 8. Re: Calculate % of total on a Column with AGG Measure
                        pooja.gandhi

                        Hey Romil!

                         

                        Appreciate your effort of creating a dummy dataset, I am back to my initial question though, the results do look right to me like I said in my 1st post. Wouldn't 136,087,683/4,805,287,410 = 2.83%? That is what you getting for platform B. I don't know what different are you expecting?

                        • 9. Re: Calculate % of total on a Column with AGG Measure
                          Romil Shah

                          Thanks for pointing that out.

                           

                          It seems the issue is different now. Not with % of total calculation.

                           

                          Third column "total" as highlighted below is incorrectly computed.

                           

                          Capture3.PNG

                           

                          Surprisingly , Grand Total is less than line item value.

                           

                          Probably, I need to change the title of the thread as well now that the issue is totally different.

                           

                          Regards,

                          Romil..

                          • 10. Re: Calculate % of total on a Column with AGG Measure
                            pooja.gandhi

                            Ok I think I know the problem. The results are absolutely right as to what you see on the view. The cell you boxed in red is the result of 4,651,518,212,268/968 = 4,805,287,409 from the 1st 2 columns. Its not the addition of values in the 3rd column itself which would be 19,274,713,182 if you click on the cell and compute using sum instead of automatic:

                             

                             

                            So what you technically want is: (sum([2015 Notional])/sum([2015 Trading Days]))/window_sum([Calculation1])

                             

                            Division by window_sum of the calculation 1 which is (sum([2015 Notional])/sum([2015 Trading Days]))

                             

                            and you end up getting correct values.

                             

                             

                            So basically for platform B it would be 136,087,683/19,274,713,182 = 0.71%

                             

                            Makes sense?

                             

                            See attached version 9.2 WB.

                             

                            Pooja.

                            • 11. Re: Calculate % of total on a Column with AGG Measure
                              Romil Shah

                              Hi Pooja,

                               

                              I found the solution.

                              I tried changing sum from automatic to window sum and it worked.

                               

                              Thanks for all the help. Have a great weekend

                               

                              Regards,

                              Romil

                              • 12. Re: Calculate % of total on a Column with AGG Measure
                                Romil Shah

                                Hi,

                                 

                                Yes, I tried a very similar thing few min back.

                                 

                                This is what I used for Column4:

                                 

                                SUM([2015 Notional])/SUM([2015 Trading Days]) / WINDOW_SUM(SUM([2015 Notional])/SUM([2015 Trading Days]))

                                 

                                Effectively it is the same thing as what you suggested.

                                 

                                Thanks,

                                 

                                Romil