3 Replies Latest reply on Jul 29, 2016 1:02 PM by Andrew Watson

# Is there a formula or other way of calculating the current filtered measure against a total of the whole data set being used?

I come from the PowerPivot world and some of the formulas allow me to create dimensions with 1 single total of the whole dataset regardless of any filters in the spreadsheet/data model. Is there any way to do this in Tableau?

The example I'm working on is a measure that displays a certain value in a dashboard title depending on what is filtered.

If the total number of customers in the dataset equals the dashboard number of customers (can be filtered to less than the dataset total) then 'All Customers', if the dashboard number of customers > 1 then 'Multiple Customers', else '[Customer]'

IF TOTAL(COUNTD([Customer])) = COUNTD([Customer]) then MAX('All Customers')

ELSEIF COUNTD([Customer])>1 then MAX('Mult. Customers') ELSE MAX([Customer]) END

The issue here is that I can't seem to find a formula or some workaround to get the total dataset number of customers regardless of the filter in my dashboard. Is there a way to do this?

• ###### 1. Re: Is there a formula or other way of calculating the current filtered measure against a total of the whole data set being used?

The {FIXED} LOD calculation will stay static. Without seeing your data try something like the following to get a count of all customers in the data set: {FIXED:COUNTD([CustomerId])}

1 of 1 people found this helpful
• ###### 2. Re: Is there a formula or other way of calculating the current filtered measure against a total of the whole data set being used?

Thanks for the response.

I'm getting a "Cannot mix aggregate and non-aggregate arguments with this function" error. I clearly have 2 aggregates unless it treats the FIXED formula as a non-aggregate.

And as I wrote that, I threw a MAX() around the FIXED formula and now I'm all set!! Thanks!!

• ###### 3. Re: Is there a formula or other way of calculating the current filtered measure against a total of the whole data set being used?

Great, happy you've managed to get it working