Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
michaelccdf
Helper I
Helper I

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

How about:

 

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

View solution in original post

4 REPLIES 4
jthomson
Solution Sage
Solution Sage

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.  

 

Capture.PNG

 

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.