5 Replies Latest reply on Nov 29, 2016 1:05 PM by sam.hong.0

    Calculations on only one value....

    sam.hong.0

      Hello all,

       

      I would like to display the calculation for only one category in my data.

       

      For example...

       

      Product Category

      - Soup (20)

      - Bread (100)

      - Milk (80)

       

      I would like to calculate percent of total for only Bread without having to filter the others so I can add as text.

       

      Is there a way to single out either a single category or group of categories (Milk, Bread)?

       

      I want to create a scoreboard sheet that would be like...

       

      Text: Bread Accounts for 50% of sales.

       

      Where the "50%" is the calculated field.

       

      Thanks!

      Sam

        • 1. Re: Calculations on only one value....
          Walt Reed

          Hey Sam,

          If you want to calculate the percent of total for each Product Category, you can use the following LOD calculation: {FIXED [Product Category]:SUM(Sales)}/{FIXED:SUM(Sales)}

           

          But if you want percent of total for only one specific category, you could use the following: {FIXED [Product Category]:SUM(IF [Product Category]='Bread' THEN Sales END)}/{FIXED:SUM(Sales)}. Then add this to Text or Tooltip.

           

          Cheers,

          Walt

          • 2. Re: Calculations on only one value....
            sam.hong.0

            Hello Walt!

             

            Thank you for the response, I think I am also there with my actual data. When I used your formula, my result is off. I apologize but I forgot to mention I want to calculate this using number of records. Not sure if that would throw anything off? In your formula, I replaced all the "Sales" with "Number of Records"

             

            { FIXED [Category]: SUM(IF [Category]='Bread' THEN [Number of Records] END)} / { FIXED:(SUM([Number of Records]))} * 100

             

            Bread - 248

             

            I have a total of 1958 records. I am expecting the value to be 12.67% but I am getting 26.12%.

            • 3. Re: Calculations on only one value....
              Walt Reed

              Hey Sam,

              I'm not sure why it wouldn't work with using number of records--it should. Try doing a count of transaction ID or something similar and see if that gives you the correct amount.

               

              Walt

              • 4. Re: Calculations on only one value....
                sam.hong.0

                Hi Walter,

                 

                I just created a test file and your original formula does indeed work. I am not sure why it is not working on my actual workbook. I'll mark your solution as correct answer as my issue might be specific to my workbook. If you have any thoughts on what could throw off the numbers, please let me know!

                 

                I tried messing with Global Filters on my test sheet and your formula works on it, so I am not sure what could be wrong with my workbook.

                 

                Thanks!

                Sam

                • 5. Re: Calculations on only one value....
                  sam.hong.0

                  Update!

                   

                  I had to click on each of my filters and click "Add to Context" and Walter's formula works perfectly.

                   

                  Thanks again Walter!

                   

                  Sam