Calculating averages on per-record basis

[matan_benyishay]

Hi,

I'm having trouble calculating averages/medians across a fact/dimension table relationship. I have a model with a table that I slice/filter by (I think this a dimension table?) called [Enrollments by day]. This is a table with one record per day of enrollment in each program that our clients have. This is connected to a fact table called [clients] (1 record per client). Clients is connected to a table called [latest income income] (one-to-one, works fine).

I have been calculating median and average income in my [latest income info] table. However, slicing the page with elements from the [Enrollments by day] table does not properly respond. If I pull income into my [Enrollments by day] table with related(), it does respond. However, what I am worried about is that this is calculating the average/median on a per-day rather than per-client basis, because those are the records.

Trying to use a formula for calculate(average(<income>), allselected(..., <ClientID>)) has not seemed to work.

Screenshots below. Suggestions?

First screenshot: model

Second screenshot: income calcs

[matan_benyishay]

OK, here goes. I recreated the necessary tables through exporting samples and re-importing static Excel files that were de-identified.

Google drive link to the PBIX 

Tables:

• Clients sample: fact table
• Enrollments by day sample: this is our dimension table
• Income: we pull income into our clients table from here

When you interact with the slicers, you can see that the average income calculated in the enrollments table changes, but the average income calculated in the clients table does not change.

My concern is that the enrollments by day table has 1 row per clients per day; I want the average to be calculated for each client, not for each client-day row.

[lbendlin · Accepted solution]

In fact your Clients table is the dimension, and the other two are the fact tables - as you show in your data model.

Therefore you should use columns from the Dimension table to control your visuals.  However when you select a ProgramName from the Enrollments table that selection will not reflect back into the Clients table. You can choose to change the link to bidirectional

so that filters in the fact table can flow back up into the dimension table.  Or you could create a measure that senses all the filters in the fact table and then computes the result in the dimension table.

Attached is the example with the bidirectional filter. See if you can get the other (better) version to work  (best done via TREATAS)

[matan_benyishay]

Thanks so much! The bi-directional filter worked. And it's good to better understand the fact and dimension tables. I was confused because the enrollments table is powering most of the slicers, but also the count of clients in most of the visuals.

[lbendlin]

Don't get used to the bidirectional filter too much - it's not considered best practice and should only be used when it has no adverse side effects, or when you have no other choice. General guidance is that dimension tables influence (filter)  fact tables, and the other way round should be avoided if possible.

[matan_benyishay]

Thanks. In general, the client table is providing dimensions for the enrollments table, such as a count of clients enrolled (enrollment table) by gender (clients table). The exception is the income, which comes from a different table. Would it be preferrable to have the income table flow directly to the enrollments table, instead of through the clients table?

[lbendlin]

That depends on the business question you want to answer with your report.  Most of the time you want to keep your fact types separate.

[matan_benyishay]

OK, if we use the TREATAS function instead, I want to check my understanding. Is the right approach:

measure = 
calculate(
<summary messure>,
treatas(
values(<identifier in fact table>),
<identifier in dimension table))

[lbendlin]

Yes, that would be the approach.

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...