topic Re: Measure works on rows but not subtotals in Matrix in DAX Commands and Tips

Measure works on rows but not subtotals in Matrix

Matt_HD — Tue, 26 May 2020 08:10:00 GMT

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

Re: Measure works on rows but not subtotals in Matrix

AlB — Tue, 26 May 2020 08:16:04 GMT

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

Re: Measure works on rows but not subtotals in Matrix

Matt_HD — Wed, 27 May 2020 08:04:41 GMT

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.