Customer Complaint Report

[cbruhn42]

I am trying to build a report showing customer complaints by lot number and am having some trouble figuring out the best way to display the data.

I have two tables:

Customer complaint table

Lots shipped table

The tables are linked on lot number so I have a many to many relationship.  I have a date field in both tables for the date the complaint was filed and also the date the lot was shipped.

I want to be able to display a metric of customer complaints per million containers shipped by both total overall and by lot number.  We review our customer complaint data monthly, so I'd like to be able to slice by date and display which complaints came in that month, display the complaint per million container shipped for that date range and also the total complaint per million containers shipped for each lot regardless of date range selected.  Or if you have any other suggestions on how to best display this data I'm open to suggestions as well.

thank you.

[AllisonKennedy · Accepted solution]

@cbruhn42 , do you have a DimDate table already to do the month summarization? https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html 

I would then also create a DimLot table that has a 1 to many relationship to both your current tables on Lot Number, this will make the summarization possible that you can't acheive with the many to many. https://excelwithallison.blogspot.com/2020/08/its-complicated-relationships-in-power.html