Calculate Custom Row Subtotal in Matrix Visual

[ZiyadSyauqi]

Dear Power BI Community,

I need help creating a custom row subtotal in a Matrix Visual. Specifically, I want to calculate the vs_n_min_1_pct column by dividing tm_actual by lm_actual. Here’s a brief overview:

• Current Result: The row subtotal directly sums all values within the subgroup (summing metrics values inside group_metrics).
• Desired Result: For the row subtotal of Group2 in the column vs_n_min_1_pct, it should be 116.8%, which is calculated as 5,904,649 (tm_actual) divided by 5,055,722 (lm_actual) multiplied by 100%.

Data Sample

Here’s a simplified version of my data:

group_metrics	metrics	Attribute	Value
Group1	A1	lm_actual	503,347
Group2	A2	lm_actual	5,113,225
Group2	B2	lm_actual	-387,922
Group2	C2	lm_actual	270,220
Group2	D2	lm_actual	19,299
Group2	E2	lm_actual	40,900
Group1	A1	tm_actual	446,129
Group2	A2	tm_actual	5,023,533
Group2	B2	tm_actual	589,795
Group2	C2	tm_actual	267,103
Group2	D2	tm_actual	13,253
Group2	E2	tm_actual	10,965
Group1	A1	vs_n_min_1_pct	88.60%
Group2	A2	vs_n_min_1_pct	98.20%
Group2	B2	vs_n_min_1_pct	-152.00%
Group2	C2	vs_n_min_1_pct	98.80%
Group2	D2	vs_n_min_1_pct	68.70%
Group2	E2	vs_n_min_1_pct	26.80%

Current Results Screenshots:
This is what i put in my Matrix Visuals:

and this is the results

Expected Calculation

For Group2, the subtotal for vs_n_min_1_pct should be 116.8%, calculated as follows:

vs_n_min_1_pct_total = (5,904,649 / 5,055,722) × 100% = 116.8%

Issue

While this code works for individual metrics, the row subtotal directly sums up all values within the subgroup. Instead, I want to retain the calculation for tm_actual and lm_actual and specifically calculate the custom row subtotal for vs_n_min_1_pct.

Question

Is it possible to achieve this in Power BI? If so, how can I adjust my DAX measure or the matrix settings to calculate custom row subtotals as described?

Thanks!

[v-huijiey-msft · Accepted solution]

Hi @ZiyadSyauqi ,

Thanks for the reply from @ridhoaryo24  / @amitchandak .

Please try:

Enter Power Query and pivot the Attribute column:

This is the data after pivoting:

Exit Power Query and create a measure:

Measure = 
VAR _tm = SUM('Table'[tm_actual])
VAR _lm = SUM('Table'[lm_actual])
RETURN
IF(
    HASONEVALUE('Table'[group_metrics]),
    DIVIDE(SUM('Table'[tm_actual]),SUM('Table'[lm_actual])),
    DIVIDE(_tm,_lm))

After creation, set the format:

Drag the following fields to the Matrix visual object:

The final page effect is as follows:

If you have any other questions please feel free to contact me.

The pbix file is attached.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[v-huijiey-msft · Accepted solution]

Hi @ZiyadSyauqi ,

Thanks for the reply from @ridhoaryo24  / @amitchandak .

Please try:

Enter Power Query and pivot the Attribute column:

This is the data after pivoting:

Exit Power Query and create a measure:

Measure = 
VAR _tm = SUM('Table'[tm_actual])
VAR _lm = SUM('Table'[lm_actual])
RETURN
IF(
    HASONEVALUE('Table'[group_metrics]),
    DIVIDE(SUM('Table'[tm_actual]),SUM('Table'[lm_actual])),
    DIVIDE(_tm,_lm))

After creation, set the format:

Drag the following fields to the Matrix visual object:

The final page effect is as follows:

If you have any other questions please feel free to contact me.

The pbix file is attached.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[ZiyadSyauqi]

Thanks @v-huijiey-msft i think your solution also works!

I already reply @ridhoaryo24 comment for another workaround without needed to do pivoting the table. My actual data need some additional "dynamic column header title" using DAX Columns as the column field so i think it is better not pivoting the data.

[ridhoaryo24]

Hi Ziyad,

You can make these measure to get the result you want

lm_actual = CALCULATE(SUM('Sheet1 (2)'[Value]), FILTER('Sheet1 (2)', 'Sheet1 (2)'[Attribute]="lm_actual"))

tm_actual = CALCULATE(SUM('Sheet1 (2)'[Value]), FILTER('Sheet1 (2)', 'Sheet1 (2)'[Attribute]="tm_actual"))

And then, you make this measure:

vs_n_min_1_pct =
VAR lm_group = CALCULATE(
[meas_lm_actual],
ALLEXCEPT('Sheet1 (2)', 'Sheet1 (2)'[group_metrics])
)
VAR tm_group = CALCULATE(
[meas_tm_actual],
ALLEXCEPT('Sheet1 (2)', 'Sheet1 (2)'[group_metrics])
)

Add those calculations to the Values accordingly.

There is no need to insert anything in the Columns.

May it help you.

Regards,

Ridho A.

[ZiyadSyauqi · Accepted solution]

Thanks using some additional DAX code and combine with your solution i got this solution:

ValueNew = 
VAR lm_actual = CALCULATE(
    SUM(Table[Value]),
    Table[Attribute] = "lm_actual",
    ALLEXCEPT(Table, Table[group_metrics], Table[metrics])
)

VAR tm_actual = CALCULATE(
    SUM(Table[Value]),
    Table[Attribute] = "tm_actual",
    ALLEXCEPT(Table, Table[group_metrics], Table[metrics])
)

VAR lm_group = CALCULATE(
    SUM(Table[Value]),
    Table[Attribute] = "lm_actual",
    ALLEXCEPT(Table, Table[group_metrics])
)

VAR tm_group = CALCULATE(
    SUM(Table[Value]),
    Table[Attribute] = "tm_actual",
    ALLEXCEPT(Table, Table[group_metrics])
)

RETURN 
SWITCH(
    TRUE(),
    SELECTEDVALUE(Table[Attribute]) = "vs_n_min_1_pct" && HASONEVALUE(Table[metrics]),
            DIVIDE(tm_actual, lm_actual, 0),
    SELECTEDVALUE(Table[Attribute]) = "vs_n_min_1_pct" && NOT HASONEVALUE(Table[metrics]),
            DIVIDE(tm_group, lm_group, 0),
    SUM(Table[Value])
)

[amitchandak]

@ZiyadSyauqi , You can refer the blog how to shift sub total

Power BI How to get the P&L formatting right: https://youtu.be/C9K8uVfthUU
Power BI How to get two columns format Profit and Loss Statement(P&L) right: https://youtu.be/WLg85yiMgHI
https://medium.com/microsoft-power-bi/power-bi-formatted-p-l-with-custom-sub-totals-and-blank-rows-e...