Forum Discussion

WillSmyth's avatar
WillSmyth
Copper Contributor
Jun 22, 2023
Solved

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

16 Replies

    • WillSmyth's avatar
      WillSmyth
      Copper 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

Resources