Which takes precedence - TOTAL qualifier or Set Analysis?
I have a pivot table, dimension "company", and expressions for total sales for each month, a year total, and the % of each company's sales for the total. The problem is computing the percentage, both on the company level, and at the chart level. For all my calculations, I don't want to include the TAX GROUP, which is in a different business but whose records are in my table (and which I can't exclude at the script level, as mgmt. wants to see their report in a separate tab).
Here's the formula: (note: Year Sales is just the sum of the monthly sales by company, and it computes correctly)
% of sales = [Year Sales]/sum({<company-={'TAX GROUP'}>} TOTAL if(Class='Broker',extprice))
If I include TOTAL in the expression, my denominator DOES include the Tax Group. However, if I exclude it, instead of the chart
showing (e.g.) if company XYZ has $10,000 out of $1 million total sales "1%", it shows "100%", and it does that for every company,
when what we want to see is
ABC $35,000 3.5%
DEF $52,000 5.2%
XYZ $10,000 1.0%, etc.
What I want in the denominator is the total sales for ALL companies EXCEPT Tax Group. How would I show that?
