6 Replies Latest reply on Jul 12, 2018 4:41 AM by Sandra Braknyte

    % of Total using Table (across and down) in a calculated field

    Sandra Braknyte

      Hi all

       

      I am using a parameter so I have to use a calculated field but I am struggling to find a way to set up a formula so that it shows % of Total value across the whole table. Using Table calculations I just select "using Table".

       

      The formulas I tried:

       

      (SUM([Total - FY19])/SUM({ FIXED [Brand],[Channel],[Sector]:SUM([Total - FY19])}))*100

       

      (SUM([Total - FY19])/Total(Total - FY19]))*100 and select Action (Brand, Sector, Channel) in the "Table calculations" link.

       

      None of them calculate correctly.

       

      The top label shows using table calculations, the bottom label uses parameter and formula as above. The grand totals should add up to 100 across the whole table, not the across rows.

      Capture.PNG

       

      If this gets answered, maybe someone knows how to set up different formats within a parameter? My parameter allows to choose between absolute values and % and I cannot get them to show as % (hence why I am multiplying by 100 in the above).

       

      Thanks

      Sandra

        • 1. Re: % of Total using Table (across and down) in a calculated field
          Abhinav Garg

          Hi Sandra,

           

          It's hard to work on the Calculated Field without dataset. Could you possibly attach workbook?

           

          To answer your last part: Yes, you can show Parameter to %.

           

          Use custom formatting "0\%"

          It will append percent at the end of the number.

          Keep in mind that Custom works exactly like Excel. SO anything that Excel format supports, it is available here.

           

          Cheers!

          • 2. Re: % of Total using Table (across and down) in a calculated field
            Sandra Braknyte

            Thanks Abhinav. I am not in front of my Laptop, will try and recreate this on sample data and post it here.

             

            As for the formatting. Does it then change depending on whether in the parameter I select an absolute figure  (formatted as number) or % figure (formatted as %)?

             

            Cheers!

            Sandra

            • 3. Re: % of Total using Table (across and down) in a calculated field
              Abhinav Garg

              Yes, it does change.

               

              Let me take an example here: So let's say the value of my Parameter is '20'.

              Now if you format it as a Percent - then Tableau will show you it as 2000%

              On the other hand, if you have it as the number and use the custom formatting as I showed above, it will display it as 20%.

               

              There is nothing right or wrong here, depending on what you want to do, use the format accordingly.

              The only thing you want to keep in mind using this in a calculated field is that when you use the percent format, the number is already multiplied by 100.

               

              Cheers!

              • 4. Re: % of Total using Table (across and down) in a calculated field
                Sandra Braknyte

                Thanks for your help but I am afraid I had no luck...

                 

                Formatting:

                I don't even get an option for formatting parameter as in your screenshot because I am using a "List" of values. But just to clarify, what I am looking for is:

                    1. When parameter is "Cost", it shows a number that is for example cost and it should be formatted as number e.g. $10m,

                    2. When parameter is "% of Total", it shows a % (I use a calculated field) as it is a cost as % of total for example and it should be formatted as 2%

                 

                I hope it clarifies it better.

                 

                Calculating across table with the parameter:

                I think I solved it in the attached workbook. It did not work on my model though because of the Year filter. I solved it for using {fixed ..} and using all the dimensions including the Year. Unless there is another way around this without a LOD expression?

                 

                Sandra