Cannot change the default Column SubTotals functionality

[renjivp]

When enabling column subtotals, automatically the Sub totals column appears and displays sum of other columns (eg Jan-23 & Feb-23). The value for Actual Total is correct, which is total of respective columns, but for Ratios Total the calculation need to be different (Actual Total - Direct Cost / Actual Total -Revenue * 100) instead of sum of ratios of columns. Is there an option to modify using column formula or other work around

Kindly advise.

[SanderSandvik]

Hi, I managed to recreate your scenario with some sample data, and found a workaround for this issue. For this solution you will have to adjust your ratio DAX measure. I used Allexcept to filter out any context for the matrix except for the wanted row, in this case Revenue and Direct Cost. And returning the value on the same row as Direct Cost using Selectedvalue. I have attached a picture of the resulting matrix and the DAX code, hopefully this will help you to apply this into your own matrix

Ratios = Var Sales = CALCULATE( [Actuals], ALLEXCEPT(Dim_ReportLayout,Dim_ReportLayout[ReportLineJSON]), Dim_ReportLayout[ReportLineID] = "IS01" ) Var Cost = CALCULATE( [Actuals], ALLEXCEPT(Dim_ReportLayout,Dim_ReportLayout[ReportLineJSON]), Dim_ReportLayout[ReportLineID] = "IS04" ) Var Diff = Sales - Cost RETURN IF( SELECTEDVALUE( Dim_ReportLayout[ReportLineID]) = "IS04", DIVIDE(Diff, Sales, BLANK())-1, BLANK() )

Best Regards, Sander

[renjivp]

Hi SanderSandvik,

Thank You for your prompt support!

Sorry that i didn't attach the complete Report. There are 3 more columns that need to be calculated against the revenue, attaching for your reference. There are sub levels also for each.

Would you please share the sample PowerBI Project file also for reference.

Best Regards Renji