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?

    Dev Brightlabs

      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.