Hi all!
I am having troubles converting some data stored in an Excel table into a meaningful pivot table.
The table contains data from post-sales surveys with clients + other internal and external data.
I have built the table ('original table' tab in the attached Excel) so that every row represents a sales opportunity. There are columns for the client name, the outcome (win or loss), the date, the region, the product sold, and the competitor that won or lost.
Troubles start with columns H3 to M3. Each client can select up to 3 reasons for which they chose our product or, alternatively, a competitor's product. I have created a column for each possible reason. While this makes it easy to filter the data in the table; I am unable to aggregate this data with a pivot table (e.g. I cannot aggregate all win reasons by region or client into a single pivot table column) and I am forced to do manual calculations every time I need to do some analysis.
So I thought I was maybe using the wrong table and I have created a new one that has a row for each win/loss reason a client selected ('new table' tab in the Excel).
While I can finally summaries win/loss reasons through a pivot table now; I have lost the ability to count unique opportunities (e.g I cannot say how many times a specific client hasn't chosen us, as client names are repeated several times for the same opportunity).
Is anyone able to suggest a way to overcome this?
Thanks a lot!
Bookmarks