Measure works on rows but not subtotals in Matrix

[Matt_HD]

Hi

I have some data (all on one table) that i'm summarising in a matrix

The table is as follows ( other columns redacted) there are many rows and the name is 'CTS Budget Course Volume'

Course    Value  Budget Total Attendees  Trainer Ratio  Capacity per Course   Date

H&S          20                      9                           1                           20                    01/04/2020

The manual calculation for Occupancy would be [Budget Total Attendees] / [Trainer Ratio] x [Value] x [Capacity Per Course]

I have converted to DAX as follows

Occupancy Budget (%) =

DIVIDE(

    SUM('CTS Budget Course Volume'[Budget Total Attendees]),

    SUM('CTS Budget Course Volume'[Capacity per Course])*sum('CTS Budget Course Volume'[Trainer Ratio])*sum('CTS Budget Course Volume'[Value])

)

However on a matrix visual this works perfectly for individual courses, but I have a Date Row and the subtotals/totals dont work properly......they display 0%

Any ideas?

There is a course master and date master table in the model with a simple 1 to many retalionship back to this table and this works fine accross the reso of the model so I suspect my issue is with the DAX above.

Thanks for your help

[AlB]

Hi @Matt_HD 

Try this. I assume you want the total to add up the individual rows results. 

Occupancy Budget (%) TOT =

SUMX( SUMMARIZE(Table1, Table1[Col1], Table1[Col2], add all other columns in the visual here), [Occupancy Budget (%)] )

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Matt_HD]

Hi, that doesnt work im afraid as it just adds up all the % ages into a total, the answer should be 55% and that gives 2241%

I need it to apply the same calculation to the subtotals/totals as the rows, not add up all the rows calculations.