Mission #02


In this new mission we will work with Pedro’s other team, the B2B Sales analysts. If you don’t remember how the division of Pedro’s management team works, I suggest you review the page with the Customer Briefing.

One of the tasks of the B2B Sales team at the beginning of every month is to separate Net Sales by Product Category and by Sales Supervisor. Each supervisor must receive an Excel file with the sales of their region, where there is a sheet for each Product Category.

Analysts create these reports in Excel pivot tables through a direct connection to the database that IT has left programmed in an Excel file.

This generates a huge repetitive workload at the beginning of the month, as analysts have to update each file manually and send them by email to supervisors.

On the other hand, Pedro needs to receive from his team only a consolidated file with all supervisors and all product categories. Besides, he requested that, in this consolidated file, an analysis be presented of which months each region has reached its monthly sales target.

To assist us in this process, he provided us with a file called Target.xlsx, which is a manual base for registering supervisors by regions and their respective targets by month, since the company’s ERP does not allow this registration of goals by supervisor neither by region.


Your goals in this mission are:

  1. Import into Power BI all four files for the individual supervisors and consolidate the data into a single table within Power BI.
  2. Import into Power BI the table with the monthly target by region and perform an analysis of Net Sales x Target for the supervisors, to know in which months each of the supervisors reached the target.
  3. Create the necessary transformations to build the proper data model using relationships between tables.
  4. As much as we can do this consolidated analysis that Pedro asked for, the process is still far from ideal. Besides, do you see other problems that Pedro may face when analyzing the values coming from the Marketing team, from Mission #01, and the values coming from the Sales team, from Mission #02? What would you suggest to him to minimize these risks?

The Mission will only be considered concluded if you complete the 04 items in the list above.