Dear team,
I'm grateful if you could take a look at the attached files and help me to combine them for an analysis.
The first file '2019_test' includes customer numbers, products groups, Actuals 2019 and Sales dates.
in the second file '2019 Events_test' there are Events with dates listed, customer numbers of the attendees and the product group that was discussed.
I would like to combine both Excel tables so I can analyse, if Actuals of the customers who attended an Event increased after the Event or not.
Idealy, in a pivot table there should be the Events listed, the attending customers and the Actuals of these customers per months in 2019. And if possible, also a comparisson of the Actuals before and after the event.
Unfortunatelly, some customers attended several events (even in the same product group), so they are not unique in the Event table.
Is there a way to match the tables to get the desired analysis?
Thank you so much in advance for your help!
Kind regards,
Claudia
Hi Claudia,
Welcome to our forum. Please see file attached.
Mynda
Hi Mynda,
many thanks for your quick reply!
What I would need in addition is to see the event name, the customers who have participated in the event and their Actuals in 2019.
And ideally, the possibility to filter with a slicer the event, to get all attendees of the filtered event and their Actuals.
Is this possible, too?
Thank you so much in advance,
Claudia
Hi Claudia,
I don't think your data is structured correctly for that. Take customer 7996 for example. They attended two events. Which event do you assign their sales to?
If you put the following fields in the Row labels in that order and leave the other fields as I had them in the file I uploaded. I think that'll be the closest you'll get to what you want.
- Customer Number
- Event
- Values
Feel free to move the fields around to find something that suits your needs better, but keep in mind the constraint I mentioned above.
Mynda
Hi Mynda,
Thanks for your reply.
I knew that the data is not structured to fit to the 'usual' match of tables. That's why I already mentioned in my request 'Unfortunatelly, some customers attended several events (even in the same product group), so they are not unique in the Event table.'.
I just hoped that you may know a special 'trick' that I'm not aware of, yet.
Is there another 'trick' in powerpivot, to have a 'flexible' column that looks up values from other columns, depending on a prompt?
Many thanks again,
Claudia
It's not something fixable with a trick, so to speak. Let me put it this way, if you take customer 7996, how would you like the data presented?
If you can put rules around how you want Excel to treat customers who attend several events and how you'd apportion sales to those multiple events, then it may be possible to build it in Excel, but you need to decide what those rules are. It's not something I or Excel can determine.
Mynda
