Forum Discussion

matan_benyishay's avatar
matan_benyishay
Icon for Helper II rankHelper II
3 years ago
Solved

Calculating 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 [Enrollmen...
Need Help
  • lbendlin's avatar
    lbendlin
    3 years ago

    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's avatar
matan_benyishay
Icon for Helper II rankHelper II
3 years ago

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's avatar
lbendlin
Icon for Super User rankSuper User
3 years ago

Yes, that would be the approach.