3 Replies Latest reply on Jul 20, 2017 6:43 PM by Zhouyi Zhang

    (: Help: How to get correct %of Total, while adjusting filters

    Jennifer S

      Hi Everyone! Can somebody pleeeeeeze help me?

      -

      OVERVIEW
      •In Fig 1 below, the final measure/metric of interest is the yellow-highlighted AGG(% of Total). This is a calculated field from SUM(Number of Records) / SUM(Calc Number of Records).
      •The AGG(% of Total) in Fig 1 seems to be correct based on the numbers displayed above the % value(s): (1/1 = 100% and 3/4 = 75%)
      •However, I can't get the numerators and denominators to the values I need when adjusting the filters

       

      DETAILS
      •I need the numerator (SUM(Number of Records) to change based on all filters (*Date, *Food Type, *Quality Tier, and *Slate, Organic), shown in Fig 1
      •I need the denominator (SUM(Calc Number of Records) to change based only on the asterisk filters (*Date, *Food Type, *Quality Tier, and *Slate), shown in Fig 2
      •So for Fig 1, the numerator = correct, the denominator = incorrect
      •But in Fig 2, the denominator (yellow highlights) = correct, the numerator = incorrect

       

      HOPEFUL END RESULT

      How can I get the table (per Fig 1) to show for the MEAT and Q2 cross section:
      •the value of 1 for (SUM(Number of Records)
      •the value of 2 for (SUM(Calc Number of Records)
      •And finally, the value of 50% (from 1/2) for AGG(% of Total) ?

       

      And for the VEGGIES and Q2 cross section:
      •the value of 1 for (SUM(Number of Records)
      •the value of 4 for (SUM(Calc Number of Records)
      •And finally, the value of 25% (from 1/4) for AGG(% of Total) ?

       

      NOTE:

      • I don't know if it's possible to keep the 3 numbers in a single sheet and in a single column OR if I should try to create a separate sheets and then show everything side-by-side in a dashboard (which is fine, too!)
      • I know the filters query the data source first, and then table calcs happen after, but still can't figure a way to make everything work
      • I attached the packaged workbook with the one sheet
      • The data set has 30 records

       

      THANK YOU! THANK YOU!

       

      Fig 1. Shows Correct Numerator but wrong Denominator

       

       

      Fig 2. Shows Correct Denominator but Wrong Numerator