cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
michaelccdf Regular Visitor
Regular Visitor

cumulative total of measure

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!

1 ACCEPTED SOLUTION

Accepted Solutions
mattbrice Senior Member
Senior Member

Re: cumulative total of measure

How about:

 

Total Periods per Homeroom = CALCULATE (
    SUMX ( VALUES ( Table[Week] ), [Measure per periods Homeroom] ),
    FILTER (
        ALL ( 'ccDates'[Date] ),
        'ccDates'[Date] <= MAX ( 'ccDates'[Date] )
    )
)
4 REPLIES 4
Super User
Super User

Re: cumulative total of measure

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

michaelccdf Regular Visitor
Regular Visitor

Re: cumulative total of measure

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.  

 

Capture.PNG

 

 

mattbrice Senior Member
Senior Member

Re: cumulative total of measure

How about:

 

Total Periods per Homeroom = CALCULATE (
    SUMX ( VALUES ( Table[Week] ), [Measure per periods Homeroom] ),
    FILTER (
        ALL ( 'ccDates'[Date] ),
        'ccDates'[Date] <= MAX ( 'ccDates'[Date] )
    )
)
Michael-Lowden Frequent Visitor
Frequent Visitor

Re: cumulative total of measure

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.