Forum Discussion
RelatedTable
I read your first post again and understud.
Try this way:
1. Edit relationship between factClosures and dimSchools and set Filter Direction = "To both tables"
2. Replace Measure formula:
PeriodsClosed Per Homeroom =
CALCULATE (
DIVIDE (
SUM ( factClosures[PeriodsClosed] );
SUM ( dimSchools[CountofHomerooms] )
)
)
Hello.
Follow steps below:
1. Add tables to Power BI Desktop
2. Create relationship between factClosures and dimDates, and between factClosures and dimSchools
3. Create claculated column in table factClosures using formula:
CountofHomerooms=RELATED(dimSchools[CountofHomerooms])
4. Create Measure
PeriodsClosed Per Homeroom =
CALCULATE (
DIVIDE (
SUM ( factClosures[PeriodsClosed] );
SUM ( factClosures[CountofHomerooms] )
)
)
thank you popov, this is almost what I am looking for. The difficulty is that I only need the total homerooms for schools that reported in a given week. Something like
sum(all the periods closed in a given week) / sum(homerooms of distinct schools that reported in a given week)
in other words, I need to group by week and school
does that make sense? Thanks a lot for the help.
I read your first post again and understud.
Try this way:
1. Edit relationship between factClosures and dimSchools and set Filter Direction = "To both tables"
2. Replace Measure formula:
PeriodsClosed Per Homeroom =
CALCULATE (
DIVIDE (
SUM ( factClosures[PeriodsClosed] );
SUM ( dimSchools[CountofHomerooms] )
)
)wow, this works great! Setting cross filter direction to both unlocks the magic! Thanks so much.
