I am struggling with how to figure out averages and medians and include 0s from my data set.
There can be zeros I want to use either:
- when there is a record in the data showing a product quantity purchased of 0 (strange but true) [example in the attached: Customer I, Product 1)
- when the customer doesn't purchase the product, so I want to assign "0" quantity to them [example: Customer G, Product 1]
A tricky items (to me):
the same product can appear sold to the same customer multiple times, like when Customer A purchases Product 1 in both California and Washington, there is a different row for each states. But when I calculate averages and medians, I want to only calculate by customer (so use the sum of California and Washington sales).
I am going to assume each customer has the same length contract for all of their products (even if across multiple states).
My end goal is to have a subset of products from the data (say show just Product 1 and Product 3):
- Total bookings [I think I am doing this correctly]
- Total annualized bookings (so converting every bookings into a yearly figure, which will mean dividing by the number of years…even if the contract is only a fraction of a year, that is OK) [I think I have correct]
- Average annualized bookings [I couldn't figure this out, since I need to calculate the average over all of the customers, even if there isn't a row for the product, like for Customer G and Product 1..right now the average excludes that customer since there are no annualized bookings, even 0, in the data]
- Median annualized bookings [wrong either way I tried – using MEDIAN and also a calculated field with WINDOW_MEDIAN]
- Bonus one: the unit quantity that is greater than or equal to the unit quantity of 80% of the customers [zero clue how to do this]
Then I'm going to do all of the above again, in the same worksheet (so not use a filter to toggle between the sets of customers), but for only customers with >=12 month contracts. I've started to set up those too.
Any advice would be great. Thank you –
tableau-20160509-ask.twbx 80.5 KB