5 Replies Latest reply on Dec 9, 2016 8:20 AM by Mahfooj Khan

# calculation based on filter selection

Hi All

I have sell data to analysis. I need to calculate % of orders shipped by orders received like below –

(Ship/Receive) * 100

My problem is – I am not able to calculate it according to gender and purchase type filter.

Let’s say for city Dallas

1. If all the filter values selected then  (372/20503) * 100
2. If Only purchase type “By Phone” is selected  (44/20503 ) * 100
3. If  purchase type “By Phone” is selected  and Gender type Male selected  (44/6799 ) * 100

Additional Question -

I would also like to hide nulls from quick filter. It does not seem to be possible, as per many posts online. If anyone has any suggestion to hide nulls in quick filter, Please suggest as well.

I am attaching sample twbx file as well.

Thanks

Dale

• ###### 1. Re: calculation based on filter selection

Hi Dale,

Using LOD you can achieve this easily, find my approach

I've create two calc fields

City wise Ship:

{FIXED [City],[Purchase Type],[Gender]:SUM([Order_Ship])}

City wise Order receive:

{FIXED [City]:SUM({FIXED [City],[Purchase Type]:SUM([Order_Receive])})}

% of Ship:

SUM([City wise ship])/SUM([City wise Order receive])

Now put context on your City and Gender fields. Just right click and "add to context"

Now drag your field and check with filters

Condition 1. When you've all filters then (372/20503)

.

Condition 2: Purchase Type: "By Phone" then (44/20503)

Condition 3: Purchase Type: "By Phone" and Gender: "Male" then (44/6799)

I hope this is what you wanted? Workbook attached for your reference. Let me know If you've any query.

Mahfooj

1 of 1 people found this helpful
• ###### 2. Re: calculation based on filter selection

Perfect. Thanks for quick reply.

Can you please  help me on below as well.

I would also like to hide nulls from quick filter ( Gender and Purchase type). It does not seem to be possible, as per many posts online. If anyone has any suggestion to hide nulls in quick filter, Please suggest as well.

Thanks again

Dale

• ###### 3. Re: calculation based on filter selection

Sure! Just duplicate your Gender field and drag it to filters shelf and select "null" and exclude. See the screen shot

Now go to your original Gender quick filter which you've in your worksheet. From the right side down arrow select "only relevant values"

Then You wont get any null in your Gender filter. One more thing the duplicated field on context too.

Hope this help.

Mahfooj

1 of 1 people found this helpful
• ###### 4. Re: calculation based on filter selection

Thanks Mahfooj for all your help and suggestions.

• ###### 5. Re: calculation based on filter selection

You are welcome Dale!