Help with building a project (creating relationships)

[powerbi92614]

I manage customer escalations for my company and the process is 100% manual. Currently, my co-worker and I use three google sheets to log each escalation we receive.

• The first google sheet is titled “2019 Customer Escalations” and there is a tab for each month. This is where we log each customer escalation.
• The second google sheet is titled “2019 Store Details” and it’s also split up into monthly tabs that cover all our store’s information for that given month. The information for these store change monthly which is why we can’t use one tab for the entire year.
• The third google sheet is titled “Manager Detail Report”. It goes over the information for each store manager and how many stores they are managing for that given month. Unfortunately, this information also changes frequently which is why we update it monthly.

I have converted all of these google sheets into excel documents and loaded them into Power BI. The issue I’m having is creating a relationship between all these documents. Here are the documents I have so far:

1. January Manager Detail Report
2. January Store Details
3. January Customer Escalations
4. February Manager Detail Report
5. February Store Details
6. February Customer Escalations
7. March Manager Detail Report
8. March Store Details
9. March Customer Escalations
10. April Manager Detail Report
11. April Store Details
12. April Customer Escalations

What I would like to do is have one Power BI report that I can send to an end user once a month that will show how our escalations are doing. I would like the report to have about 14 tabs by the end of the year. I’m envisioning a tab that collects all the data we have so far and outlines the annual analytics. This tab would show you at a glance a numerical value of many escalation each store received for a given month, it would also have a bar graph so you can see what the growth looks like, etc.

The second tab would outline all the managers and how many stores they were managing each month. I’m visualizing being able to click on a manager’s name and then having a table that shows their count and how it has changed each month.

The remaining tabs would be monthly tabs that break down the escalations. Each monthly tab will show you the details of the escalations for that given month. You’d be able to see the category for each escalation, which manager received the most escalations, which store received the most escalation, etc.

I’m stuck on how to best build this. I’m new to Power BI and have taken the Microsoft training and plan on taking more training; however, I learn best while diving in.

Currently, I only have 6 excel documents loaded in Power Bi. Basically, the first 6 in the above break down. I am trying to create a relationship between each report; however, I’m now getting an error that reads “You can’t create a direct active relationship between February Store Details and 4February Manager Detail Report because that would introduce ambiguity between tables”.

Any guidance would be greatly appreciated!

[MFelix]

Hi @powerbi92614,

There are several things that you should do but first of all you should append all your similar tales into 1 table so you would only get 1 table of each:
Manager details
Store detaisl
Escalation

Taking into account that you have the information by month you should also add a date column to your tables with beginning or ending month date and a calendar table to relate the 3 tables.

Another dimension tables like store, manage or similar probably would also fit your needs, creating a star shape model.

Without additional information is difficult to give more than this.

Can you share a sample or mockup data?

Regards,
MFelix