Averaging Data on Partially Related Tables

[esanning]

I have three tables - Application, CreditBureauResults, and FundedLoan. The table relationships are as such (Image in supporting images):

• Application - 1:* - CreditBureauResults   -   (ApplicationNumber = ApplicationNumber)
• Application - 1:1 - FundedLoan   -   (ApplicationNumber = ApplicationNumber)

Not every Application becomes a FundedLoan.

Issue: When trying to report on FundedLoans for a period (Monthly) I am trying to report the average credit score for that month (credit score is stored in the CreditBureauResults table). Within Power BI when I create this it actually just averages ALL credit scores within the CreditBureauResults table ignoring that I'm attempting to show records from a certain month. 

Also in the supporting image you can see that the table has an additional blank row, I believe all this is telling me that because there are Application's that don't have a FundedLoan record that it doesn't understand the relationship between these two tables.

What I've tried: Various DAX queries (below), as well as creating my own relationship between FundedLoan and CreditBureauResults table (ApplicationNumber column since that is the column that would be the same between a FundedLoan and CreditBueauResults records). My first attempt at a aggregated table using Power Query was also not successful, it did not change the end result.

• AVERAGEX(GROUPBY(CreditBureauResult, 'CreditBureauResult'[ApplicationNumber], "AverageScore", AVERAGEX(CURRENTGROUP(), CreditBureauResult[Score])), [AverageScore])
• AVERAGEX(SUMMARIZE(CreditBureauResult, CreditBureauResult[ApplicationNumber], CreditBureauResult[Score]), CreditBureauResult[Score])

Supporting images:

-= EDIT 2022-July-29 =-

Link to example tables with data: https://drive.google.com/drive/folders/1nN5Snhz4TQuSQz59kcfYO6NLaej_wIHs?usp=sharing

Here is what the example data would look like if it was averaging correctly:

Thank you!

[v-chenwuz-msft]

Hi @esanning ,

Is your problem solved. If yes, please mark it as the correct answer so that more can find it, if not, please add more detail about your question so that others can help you better.

Best Regards

Community Support Team _ chenwu zhu

[lbendlin]

Thank you for the concise description. Please provide sanitized sample data that fully covers your issue. I cannot help you without usable sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive. I cannot use screenshots of your source data.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

[esanning]

I have edited the original post with the additional data requested. Thank you!

[lbendlin · Accepted solution]

Your data model needs some love.

Either specify the application as the main dimension and use the other tables as facts

Or fold the FundedLoan table into the Application table.  You can leave the ContractDate and LoanNumber blank for applications that aren't funded yet.

 Your quest to show average score per month is potentially not based on any actual data in your fact tables, so you need to create measures for that which transfers the filter context via TREATAS

Generally you want to add a proper calendar table. 

See attached for a sample implementation.