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

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

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

• ###### 1. Re: (: Help: How to get correct %of Total, while adjusting filters

Hi, Jennifer

Please see my solution below

Workbook attached for your reference.

Please let me know if you have question

ZZ

1 of 1 people found this helpful
• ###### 2. Re: (: Help: How to get correct %of Total, while adjusting filters

Hi Zhouyi! This works perfectly! Thank you!!!

-Jenn

• ###### 3. Re: (: Help: How to get correct %of Total, while adjusting filters

No worries, Jennifer.

Glad it works for you.

ZZ