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:
See the report below: