Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have the table below where Measure Periods per Homeroom is the Periods divided by the Sum of Homerooms. I want to have another column with the cumulative total of this column. I can't figure out how to SUM a measure.
Note that the column totals at the bottom (32.44) give me the total of periods divided by the total of the sum of homerooms. This figure is much lower than the cumulative total of the Measure.
I tried this code but it is not working.
Total Periods per Homeroom = CALCULATE ( SUMX (ccClosures[Periods]/ccSchools[Homerooms]), FILTER ( ALL ( 'ccDates'[Date] ), 'ccDates'[Date] <= MAX ( 'ccDates'[Date] ) ) )
These forums are great, I have received a lot of help here. Thanks!
Solved! Go to Solution.
How about:
Total Periods per Homeroom = CALCULATE ( SUMX ( VALUES ( Table[Week] ), [Measure per periods Homeroom] ), FILTER ( ALL ( 'ccDates'[Date] ), 'ccDates'[Date] <= MAX ( 'ccDates'[Date] ) ) )
Can you post up the measure or logic you're using to calculate the sum of homerooms? Your total for periods looks fine, but that other column looks to just be returning the highest value in the given period - the actual division measure seems to be working fine
thanks. I added another column below, count of school, so you can better see the logic. The sum of homerooms column is the sum of all the homerooms for the schools that reported data in the given week. For example, for the week 2017-08-21, 9 schools reported. These schools have 159 homerooms. So for that week there were 1.38 periods per homeroom (219/159). This is working as expected.
The totals are also working as expected. Over all the weeks, 17 schools reported. They have a total of 284 homerooms. The periods per homeroom according to this calculation is 32.44. This is working as expected.
However, in my case, I want to add the periods per Homeroom for each week instead of calculting from the total. In other words, the table calculates 9212 / 284 = 32.44. I want to calculate 1.38+2.63+1.60 + 2.74+4.04+2.36+2.06+2.77+2.83+3.26+3.17+2.19+2.61+4.54+4.55+10.23+7.52=60.48
The latter calculation better accounts for the differences in schools reporting each week.
How about:
Total Periods per Homeroom = CALCULATE ( SUMX ( VALUES ( Table[Week] ), [Measure per periods Homeroom] ), FILTER ( ALL ( 'ccDates'[Date] ), 'ccDates'[Date] <= MAX ( 'ccDates'[Date] ) ) )
why do you need to FILTER relative to the Date? ... what if they swap the attribute column to "TEACHER_NAME" (or whatever). Then you need to rewrite your measure? ... bollucks.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |