Forum Discussion
RelatedTable
I have three tables
factClosures
Date,School,PeriodsClosed
dimSchools
School, CountofHomerooms
dimDates
Date, Week
I'm trying to find the PeriodsClosed Per Homeroom Per Week. The trouble is, the CountofHomerooms field in dimSchools does not seem to "link" to factClosures in PowerBI.
In SQL I would do something like the below to calculate this (there is probably a better way to do this, I'm not at all a SQL expert)
SELECT
Week,
SUM(PeriodsClosed) / SUM(CountofHomerooms) as PeriodsClosedPerHomeroom
FROM
(SELECT
dimDates.Week,
dimSchools.School,
dimSchools.CountofHomeRooms,
SUM(factClosures.PeriodsClosed) as PeriodsClosed
FROM factClosures
JOIN dimSchools ON factClosures.School = dimSchools.School
JOIN dimDates ON factClosures.Date = dimDates.Date
GROUP BY
dimDates.Week,
dimSchools.School,
dimSchools.CountofHomeRooms) as query1
GROUP BY week
Any ideas how I might do this in PowerBI directly ?
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] )
)
)
4 Replies
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] )
)
)
