4 Replies Latest reply on Dec 7, 2016 1:47 PM by Walt Reed

I am trying to calculate percentages of employees separated by week buckets as the column. However, I don't want to include the bucket=N/A as part of the grand total but I want those resources to be included in the denominator when calculating % of total. I've been using an exclude statement but is there a way for me to filter the N/A bucket out from my visual and but still include it in my calculation? • 1. Re: Adding a Totals Column

Nicole,

I understand the question, but could you attach your packaged workbook?

Thanks,

Shin

• 2. Re: Adding a Totals Column

Hey Nicole,

If you're going to have 'N/A' filtered out of the viz, for your grand total calculation, the calc would be:

{FIXED: SUM(IF [Bench Week Buckets]<>'N/A' THEN [Measure] END)}

To calculate % of total, you'll want a calc like:

[Measure]/{ FIXED:SUM([Measure])}

Let us know how this works for you.

Walt

• 3. Re: Adding a Totals Column

Thanks for the response Walt! Unfortunately I have other 8 other filters that are applied to the viz so I can't use FIXED. I tried using

IIF(attr([BenchWeekBuckets]) <> 'N/A', CountD([EmployeeID]), 0) / attr({ EXCLUDE [Bench Week Buckets] : CountD([EmployeeID])

Which populates N/A as 0.0% but I can't get Grand Total to show up  using the Analysis 'Show Row Grand totals'.

Any suggestions? • 4. Re: Adding a Totals Column

Hey Nicole,

I would modify this calculation as follows:

IIF([BenchWeekBuckets] <> 'N/A', [EmployeeID], 0) / { EXCLUDE [Bench Week Buckets] : CountD([EmployeeID])}

Then, when you add to the viz, change it to SUM([Calculation Name]) rather than ATTR([Calculation Name]) Let me know if that solves it for you.

Walt