Forum Discussion
Helper IICalculating averages on per-record basis
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
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)
9 Replies
- lbendlin
Super User
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-Forum/ba-p/963216
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/1447523 
matan_benyishayOK, here goes. I recreated the necessary tables through exporting samples and re-importing static Excel files that were de-identified.
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
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.