Hello,
I am relatively new to Power BI and this is my first time posting so I apologize if something isn't clear.
I am unable to get an accurate cost per person calculation as it is duplicating some of the values. My data is a bit complex (there are 92 total tables) so I will try to simplify it. The database allows users to submit information about projects. They only complete one submission per year and complete a single form to provide the total of their expenditures per year but then can upload CSVs that contain information about persons served. Each CSV upload is a unique project that the user is running. And each CSV contains aggregate data across 50 questions with each question having its own table in the database. Some users only one run project but others run multiple projects so will upload more than one CSV. This is a simplification of my data:
Financial Table
| Submission ID | Total Expenditures |
| 1 | $100 |
| 2 | $100 |
| 3 | $200 |
Individuals Served Data
| Submission ID | Project ID | Individuals served |
| 1 | 11 | 3 |
| 1 | 12 | 2 |
| 2 | 21 | 2 |
| 3 | 31 | 4 |
The tables are joined on submission ID, however not directly and I am not sure if that is the problem. There is one overarching table that looks like this
| Submission ID | Date of Submission | User | Location | Company | Status of Submission |
which I use for a lot of filtering (for example, I only want approved submissions, not rejected submissions or each report page is focused on one location)
The financial table is connected to that with a one to one relationship.
The CSV question tables are connected to the overarching table also through submission ID in a one to many relationship
When I try to calculate the cost per person, I just want to divide the total expenditure by a sum of the individuals served. So when calculating the measure and using the table visualization I want:
| Submission ID | Total Expenditures | Persons Served | Cost per person |
| 1 | $100 | 5 | 20 |
| 2 | $100 | 2 | 50 |
| 3 | $200 | 4 | 50 |
Instead I get:
| Submission ID | Total Expenditures | Persons Served | Cost per person |
| 1 | $200 | 5 | 40 |
| 2 | $100 | 2 | 50 |
| 3 | $200 | 4 | 50 |
I have temporarily solved this by the DAX expression
Measure = DIVIDE (MAX('Financial Table'[Total Expenditures]), SUM('Individuals Served Data'[Persons Served]) )
But this doesn't feel like a good long term solution.
Any assistance is greatly appreciated.
