Transforming Data correctly - A Fleet manager challenge to match fleet data with Sales data

[Anonymous]

As a Spare part sales manager I need to maximise the sales of wear and tear spare parts during periodic maintenances to increase compliance rate.

I like to build a dashboard to fleet managers to show them their performance. (Currently I am doing it in XLS which is painful and very manual long process.)

My challenge is that I cannot connect Fleet potential with Sales Data from my raw data.

How to transform / pivot raw data to match and able to show performance at site level? (The Sales data is not assigned to veichles it is only shows site level detail.)

In the linked file I showed a simplified data set to model my challenges and how I solve them in XLS today.

Thank you for your suggestions ideas how to solve this challenge.

Sample Data 

[Anonymous · Accepted solution]

Hi @Anonymous 

You don't have to transform the data. You can use measures to calculate the results easily. Below are the steps:

1. Add an additional Site table

2. Build relationships between tables like below

3. Create the following measures

Entitlement = SUM('Fleet Data'[Nr Of Veichle ]) * SUMX('Spare Parts Categories',[Yearly Usage (Total UOM)]*'Spare Parts Categories'[ASP (UoM)])

Sales = SUM('Sales Data'[ Price $])

Complaince % = DIVIDE([Sales],[Entitlement])

4. Use the Matrix visual in Power BI to place the corresponding column and measures. You will have the expected result as below. 

I have attached the sample pbix file for your reference. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[Anonymous]

Hi Jing, 

We have one complication to the model. 

What if not all spare parts fits to all cars?

I have updated the excel smaple. Here

Lets say we have electric veichles that do not need engine oil, but they need batteries. 

Thanks for having a 2nd look. 

Regards,

Peter

[Anonymous]

This is great! Thank you so much!

I am stadying your solution and embedding my real data... i hope it will work. Will update you here! 

Trully appreciated. 

[dufoq3]

Hi, your sample data can't be downloaded without access.

[Anonymous]

Hi - sorry for this. Please try now here