how to insert a "index" row in the matrix ?

[austinli37dot8]

This is the output I expect in the matrix; I try to insert a row called index, its category b/a, and this index how many b times of a, please help. Thank you.

[Anonymous · Accepted solution]

Hi @austinli37dot8 ,

I think you can custom row subtotal by measure to show index as you want.

You can transform your table as below.

Measure:

Custom Total =
VAR _VALUE =
    SUM ( 'Table'[Value] )
VAR _a =
    CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[category] = "a" )
VAR _b =
    CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[category] = "b" )
VAR _INDEX =
    DIVIDE ( _b, _a )
RETURN
    IF ( HASONEVALUE ( 'Table'[category] ), _VALUE, _INDEX )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@austinli37dot8 , refer if this way of adding custom total can help

https://medium.com/@amitchandak/power-bi-formatted-p-l-with-custom-sub-totals-and-blank-rows-e39ca82...

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