1 Reply Latest reply on Aug 30, 2017 6:12 AM by Jim Dehner

# How do you normalise data to calculate an overall total percentage?

Hi all,

I'm totally new to Tableau and have watched a few tutorials on the video page and looked over these forums, but unfortunately can't find a solution to my problem. Apologies in advance if my terminology is not quite correct. I am a developer and will try to articulate my needs.

My requirement is this: What percentage of customers have paid freight?

- a customer can have made multiple orders, so with Freight and some without

- I want to count a customer if they have ever used freight (shipping_price > 0)

What I am trying to achieve is:

- if a customer's orders history has shipping_price > 0, normalize them into a 1 count for [users_used_freight] column

- then use the [users_used_freight] column to calculate a percentage i.e.

- [users_used_freight] / [orders_total]  to display as a percentage

This seems simple, but there seems to be an issue where the total [user_used_freight] column is not calculating the grand total properly.

- notice how the Grand Total is 10.00 when there are so many more entries.

I'm not sure if this is even possible in Tableau, but I managed to achieve what I wanted in SQL. This is perhaps the most frustrating to me that I can't translate this across.

select (count(DISTINCT user_email)) as freight_users,

(select count(*) from orders) as total_orders,

(count(DISTINCT user_email)) / (select count(*) from orders) * 100 AS percent

from orders

where shipping_price > 0

Any help is greatly appreciated,

Matt.

• ###### 1. Re: How do you normalise data to calculate an overall total percentage?

Hi Dev

See the attached

I think the formula you are looking for is           [User used freight] / window_sum([User used freight])

it is a table calculation that needs to be set up as shown in the box below

and will return

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.