3 Replies Latest reply on Oct 3, 2018 10:52 AM by Joe Oppelt

    % of {Total}? I need help with a table calc!

    Vincent Baumel

      Okay friends, here's the situation. I work for Superstore, and sell in sub-categories all over the place. I'm curious though what my top sub-categories are per state. Simple enough, and I even put together a snazzy table to show me that. I concatenate the state and the subcategory so that I can get my sort correct, then filter my index() calculation down to the top 5:

       

       

      Then my supervisor came by like some kind of big shot, and was all like "Hey, let's see what % of total that is!" Ugh, sometimes I can't stand that guy.

      "Alright simple enough," I say. So I do a couple quick % of total calculations, and bring those in to calculate running table down:

       

       

      Here's the problem though! Since these % of Total KPIs are running table down, they don't represent the ACTUAL percent of total across my whole data set. My supervisor REALLY wants to know how much California Phone sales contribute to my overall sales, and I'm not quite sure how to show that in this chart. I can't just rewrite the calc as SUM([Sales])/{SUM([Sales])} since that would be mixing agg and non-agg arguments within the function. Can anyone help me out?

       

       

      Thanks,

       

      Vince

        • 1. Re: % of {Total}? I need help with a table calc!
          Jim Dehner

          hi

          is this the sort of thing you had in mind

           

           

          I used rank (at first set to 5 but changed to 20

           

           

           

          If you need to rank by year add the year(order date) before the colon in the LOD

           

           

          the the Percent is

           

          Or this if you want to avoid the table calc

           

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: % of {Total}? I need help with a table calc!
            Joe Oppelt

            You boxed yourself into a corner by making that State-Category calc.


            See attached.

            • 3. Re: % of {Total}? I need help with a table calc!
              Joe Oppelt

              What I did in there:

               

              Sheet 3.  Just use the raw dimensions.  Sheet 3(2):  I made two different overall sales by state.  One using LOD.  One using table calcs.  (They match.)  (Ignore top-20 states on this one.)

               

              Sheet (3):  Set the table calc for Top-20 states to run along State.  We get 1-through-N.  I displayed it in measures, and I put it on filters to get top-20, and I made a copy of it as DISCRETE and put it as the first pill on ROWS so it sorts the selected states for us.

               

              Sheet 4:  I made a calc to rank sales within each state.  Sheet 5:  Put it on filters to get top-5.  Made a copy discrete and put it before Sub-Category on ROWS to use it to sort.

               

              Sheet 6.  Made a series of calcs to give % within a state, % for all sales, % for each category.