ad

Scenario 4 (Applying filters only for numerator, not for denominator using FIXED( ) in Tableau 9)

Scenario 4:
Problem: Applying filters only for numerator, not for denominator using FIXED function in
Tableau 9
Description:
Lets take sample super store data set. I want to see count distinct of “Order Id” Group by “Customer Segment” and “Region” as my Denominator. Here is the tricky thing that I have two filters on filter shelf “Container” and “Department” But when I am calculating count distinct of “Order Id” only one filter should apply to Denominator that is “Container” See below:

Before applying filters:

See above if I did not apply filter then it showing Denominator and nominator both are same values.

Now I am changing “Container” filter. The filter will apply to both Denominator and Numerator.

Now I am applying “Department” filter. Now Only numerator will change. The “Department” filter wont apply to Denominator. See Below:


How we can restrict filters on denominator?

Solution:

Step 1: Create a calculate field for “Numerator” using below formula.

Countd([Order ID])

Step 2: Create a calculate field for “Denominator” using below formaula.

{FIXED [Customer Segment], [Region]:countd([Order ID])}

Step 3: Drag columns to respective shelves like shown below, make Container filter as context filter.

  • Drag “Customer Segment” and “Region” to row shelf
  • Drag “Container” and “Department” to filters shelf
  • Right click on “Container” column in filter shelf and click on “Add to Context”
  • Drag “Numerator” and “Denominator” to text shelf
  • Go to Show me and select text table

Now you can change filters and observe that “Container” filter is applying only for Denominator.

You can see that “Container” and “Department” filters are applying to numerator.

 See the report below: