You have done a good job of setting up this data model so far. I went ahead and created a table with
DistinctOwners = DISTINCT(Owners[Owners] )
Though might be better to do this type of thing in Power Query, but DAX works too.
Then using that table ( along with your other ones) I set up the data model as such:
It’s important to use the columns from those 3 Distinct tables for all your filters, slicers, rows, etc in the report. With that in mind, here’s the final output:
And this is the measure that got us there:
Total Amt Correct = CALCULATE( [Total Amount], Owners)
With Total Amount being a simple sum:
Total Amount = sum( Projects[Amount] )
Without getting too long winded, we are using the concept of expanded tables here. Since owners is on the many side of a 1:M relationship, Owners includes its’ own columns plus those of the three distinct tables. This is why it’s imperative to use the columns from the distinct tables as your filters. So what is happening is that the AccountDistinct and ProjDistinct are filtering both the Owners and Project tables. Which would be just fine and work as you would expect, but you also wanted an Owners filter. Which is why we created that new table. DistOwners filters the Owners table only. So we need to “send” that filter down to Projects. Which is why we use the Owners table ( which is filtered) as the filter for calculate…
Expanded tables arent the easiest to explain over a forum, so hopefully that make some sense!?
Here’s the pbix file:
Project OWners test Expanded Tables version.pbix (46.9 KB)