error on aggregate and non aggregate values

Question

While creating the following calculated field I am getting an error on aggregate and non aggregate values.

 if (COUNTD([Category])=1 then [Sub-Category]
else [Category] END

Note: I am using the super store data-set. So when a user selects a filter in the quick filter for category, I was expecting that the countd becomes one and shows sub category as rows in the table instead of category.

Answer 1

Create a calculated field that outputs a column to be used a the filter to select the sheet.

1. Create the following calculated field:

{ FIXED : if COUNTD([Category]) = 1 

then 'Sub Category' elseif countd([Category]) > 1
then 'Category' END}

1. Create two worksheets first one broken down by category and the next by sub category.
2. Add the Category filter to both worksheets and set to a context filter.
3. Drag the calculated field column to the filter pane. For the Category worksheet select the filter to Category and do the same for the sub category worksheet.
4. Finally drag both the worksheets into a dashboard and format them appropriately.