Sub Total and Grand Total by particular column in Power BI Desktop

[RP101]

Hello, 

I am very new to power bi and trying to create the Power BI dashbaord using PBI Desktop. Can you please guide me how to achive the expected results? 

Here is the dataset :

Client ID	Order Date	Item Number	Client Name	Client Request Type	Billing Code	Units
170003	8/1/2024	AOL123	Ross Inc	Cancel Order	6003	1
150002	8/1/2024	ORT123	ToysRUs Corporation	Add	5001	1
180005	8/6/2024	PAS123	T-Mobile NJ LLC	Cancel Order	6003	1
180005	8/6/2024	PAS124	T-Mobile NY LLC	Cancel Order	6003	1
140001	8/21/2024	ABD123	Amazon ABC LLC	Add	5001	1
222222	8/21/2024	68562A	Target	Create Order	80004	1
212121	8/22/2024	74432B	Macys	Create Order	80004	1
140001	8/22/2024	ABC124	Amazon XNY LLC	Add	5001	1
202020	8/26/2024	53620A	Lowes	Create Order	80004	1
202020	8/27/2024	40058F	Home Depot	Create Order	80004	1

Expected Results: 

The main focus is adding the sub total of UNITS by "Client Request Type" and sort the table by "Client Request Type" asc, "Order Date" asc

Client ID	Order Date	Item Number	Client Name	Client Request Type	Billing Code	Units
140001	8/21/2024	ABD123	Amazon ABC LLC	Add	5001	1
140001	8/22/2024	ABC124	Amazon XNY LLC	Add	5001	1
150002	8/1/2024	ORT123	ToysRUs Corporation	Add	5001	1
				Add  Total		3
170003	8/1/2024	AOL123	Ross Inc	Cancel Order	6003	1
180005	8/6/2024	PAS123	T-Mobile NJ LLC	Cancel Order	6003	1
180005	8/6/2024	PAS124	T-Mobile NY LLC	Cancel Order	6003	1
				Cancel Order Total		3
202020	8/26/2024	PLO123	Lowes	Create Order	80004	1
202020	8/27/2024	WWE12	Home Depot	Create Order	80004	1
212121	8/22/2024	EOS123	Macys	Create Order	80004	1
222222	8/21/2024	QQER11	Target	Create Order	80004	1
				Create Order Total		4
				Grand Total		10

Thank you in advanced !

[v-linhuizh-msft · Accepted solution]

Hi @RP101 ,

Please create a matrix with the following steps:

1. Create a matrix dragging fields into rows and values in the following order.

2. Change layout to tabular and turn on "Repeat row headers".

3. Turns off all "Show subtotal" option at all row levels except  Client Request Type and Client ID.

4. Result:

Best Regards,
Zhu
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.

[v-linhuizh-msft · Accepted solution]

Hi @RP101 ,

Please create a matrix with the following steps:

1. Create a matrix dragging fields into rows and values in the following order.

2. Change layout to tabular and turn on "Repeat row headers".

3. Turns off all "Show subtotal" option at all row levels except  Client Request Type and Client ID.

4. Result:

Best Regards,
Zhu
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.

[RP101 · Accepted solution]

Thank you Zhu

Appriciate your Reply !