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 SampleHere’s a simplified version of my data: group_metricsmetricsAttributeValueGroup1A1lm_actual503,347Group2A2lm_actual5,113,225Group2B2lm_actual-387,922Group2C2lm_actual270,220Group2D2lm_actual19,299Group2E2lm_actual40,900Group1A1tm_actual446,129Group2A2tm_actual5,023,533Group2B2tm_actual589,795Group2C2tm_actual267,103Group2D2tm_actual13,253Group2E2tm_actual10,965Group1A1vs_n_min_1_pct88.60%Group2A2vs_n_min_1_pct98.20%Group2B2vs_n_min_1_pct-152.00%Group2C2vs_n_min_1_pct98.80%Group2D2vs_n_min_1_pct68.70%Group2E2vs_n_min_1_pct26.80%Current Results Screenshots:This is what i put in my Matrix Visuals:and this is the resultsExpected CalculationFor 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%IssueWhile 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.QuestionIs 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!

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,YangCommunity 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!

Forum Discussion

ZiyadSyauqi

Frequent Visitor

2 years ago

Solved

Calculate Custom Row Subtotal in Matrix Visual

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!

Need Help

Tips and Tricks

Tutorial Requests

Anonymous
2 years ago
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!
Demo.pbix31 KB

ZiyadSyauqi

2 years ago

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])
)

5 Replies

Anonymous
Not applicable
2 years ago
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!
Demo.pbix31 KB
- ZiyadSyauqi
  Frequent Visitor
  2 years ago
  Thanks Anonymous 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

New Member

2 years ago

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

Frequent Visitor

2 years ago

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
Super User
2 years ago
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-e39ca822908a

Forum Discussion

Calculate Custom Row Subtotal in Matrix Visual

Data Sample

Expected Calculation

Question

5 Replies

Related content

Customize subtotal in matrix

Pivot Table - Subtotals - Custom Calculations

Calculate subtotal using dax

Custom Subtotal Labels In a Matrix

Matrix subtotal