Forum Discussion
Pivot to track products that are predominantly sold to one customer
Hi
We are looking to track items that are predominantly sold to a single customer. This is to reduce the risk of excess stock should the customer stop purchasing an item.
I have a data set showing item sales over financial year
Date, Customer No & Name, Item Name & Desc, Quantity, Amount
Currently, I have a pivot table with a time slicer showing sales that have been made to a unique customer (added data to data model and used distinct count) which shows
stock item, description, qty sold, value sold
This shows items that are purchased by a single customer and is working well.
However, I am looking at enhancing our reporting to show items where 90% of an item is sold to a single customer. Using customer count and sorting gives me items that have been bought by small numbers of customers but does not help show any skew/bias of sales of an item to a single customer
Thanks for any help.
Willy
See the attached version
16 Replies
- WillSmythCopper Contributor
HansVogelaar Many thanks for your explanation and sample worksheet. I have been able to replicate on a small dataset but am having problems applying in main data set. My excel data is a table (from SQL feed). Would you be able to offer help with applying your solution to table data? Using either your sample data or my larger test data set https://bch4-my.sharepoint.com/:x:/g/personal/wsmyth_bch_eu_com/ES_Rh4E5F-NJr8bDeR38MG4B2AtlcrbsVwcVOu1PynFNQw?e=hGLf5c
Thanks
Willy