Need to create subtotal column after each row of provider

[P_P2595]

Hi Team,

I am looking to achieve the following output in Power BI by adding a calculated column or measure. Specifically, I need to create a subtotal row after each provider and sum the values for the last three columns. The last three columns are calculated as follows:

1. @Indirect Session Total (Min):

   @Indirect Session Total (Min) = CALCULATE( SUM('PROGRESS NOTE'[INDIRECT TIME]), CONTAINSSTRING('PROGRESS NOTE'[PN TYPE], "NDIS"), ALLEXCEPT('PROGRESS NOTE', 'PROGRESS NOTE'[MRN], 'PROGRESS NOTE'[PROVIDER]) )

2. TOTAL 180:

   TOTAL 180 = CALCULATE( 180, ALLEXCEPT('PROGRESS NOTE', 'PROGRESS NOTE'[MRN], 'PROGRESS NOTE'[PROVIDER]) )

3. BALANCE:

   DAX

   BALANCE = [TOTAL 180] - [@Indirect Session Total (Min)]

Input:

MRN	CLIENT FULLNAME	PROVIDER	SPECIALITY	CASE FIRST VISIT DATE	@Indirect Session Total (Min)	TOTAL 180	BALANCE
1	J	C	O		20	180	160
2	C	C	O	08/07/2024 0:00	150	180	30
3	G	E	S	05/08/2024 0:00	30	180	150
4	J	H	S		15	180	165
5	E	H	S	26/08/2024 0:00	5	180	175

Output:

MRN	CLIENT FULLNAME	PROVIDER	SPECIALITY	CASE FIRST VISIT DATE	@Indirect Session Total (Min)	TOTAL 180	BALANCE
1	J	C	O		20	180	160
2	C	C	O	08/07/2024 0:00	150	180	30
SUBTOTAL					170	360	190
3	G	E	S	05/08/2024 0:00	30	180	150
SUBTOTAL					30	180	150
4	J	H	S		15	180	165
5	E	H	S	26/08/2024 0:00	5	180	175
SUBTOTAL					20	360	340

Is it possible to achieve this in Power BI? I would appreciate any guidance on how to implement this.

Thank you!

[Thejeswar]

@P_P2595 ,

You can get this view in the matrix in a straight forward fashion when we change the ordering of the columns.

And Row Subtotals should be enabled only for Provider and Speciality

Regards,

[Anonymous · Accepted solution]

Hi @ P_P2595,

You can display data using Matrix visualization.

Put /PROVIDER/ and other columns in the row options.

Put the /@Indirect Session Total (Min)/, /TOTAL/, and /BALANCE/ columns into the Value option.

Matrix Vision sums the last three column values for each Provider group.

Best Regards,

Dengliang Li

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