Complicated conditional division

[Anonymous]

Hi 👋 

The goal of this division is very simple, to see payroll/days per team per month. Data refresh has to happen twice a month at least.

When I extract the raw data from our financial tool, I get an excel file with several columns including name, date posted, category  (days or payroll) and quantity ( numerical value, same column for hrs and €). for the same time period for the same resource, two rows will be created in the same table, one for days and one for payroll. 

In order to find out what team the resources belong to, I need to use an excel spreadsheet we maintain specifying things about all the resources, including what team they belong to.

I have created a one to many relationship from the resources list column "name" to the financial data column "name, "and have added new columns on the financial data with the team from the resources list thanks to the RELATED function. 

The division I want to do was easily performed in excel by first doing a column with a lookup in the financial raw data to the team in the resources list, then creating a pivot table, and for every date and every team doing a division of the summed payroll / days. However I can't do this now as I am trying to eliminate all manual steps, so the person maintaining the report can just extract raw data and paste it in. 

Any ideas on how I can solve this? I would be so grateful!

[parry2k]

@Anonymous I would recommend to post sample data and expected output. If possible share pbix file and remove sensitive information before sharing.