Total by filtering other associated columns

[seefadeeb]

EMPLOYEE	Code	Expense Type	Total Amount
Shirley	3660	TAXI	145
Yunlei	3660	TAXI	105
Chuyue	3189	TAXI	727
Shirley	3660	MEALS	126
Chuyue	3189	MEALS	342
Tan	3660	TRAINING	161
Yunlei	3660	FEES	225
Yunlei	3660	MEALS	102
Guangke	2658	PRINTING AND COURIER SERVICES	70
Guangke	2658	MEALS	126
Guangke	2658	SUPPLIES - OTHER	259
Shirley	3660	LODGING	748
Chuyue	3189	LODGING	361

In the previous table, if we select Employee "Shirley" in the slicer filter, we should get the sum of total amount as 1226 as in the below table. It should sum total amount of all the associated codes & expense type.

In this example, Shirley has code of 3660 and expense type - Taxi, Meals, Lodging.  Now it should sum all the rows of code 3660 and expense type - Taxi, Meals and Lodging for all the employees.

EMPLOYEE	Code	Expense Type	Total Amount
Shirley	3660	TAXI	145
Yunlei	3660	TAXI	105
Shirley	3660	MEALS	126
Yunlei	3660	MEALS	102
Shirley	3660	LODGING	748
		Sum	1226

@Fowmy  @amitchandak @Ahmedx @Ritaf1983 

[Ahmedx]

This is the third variant, I recorded a video for you so that you understand how to create a model in power query

https://1drv.ms/v/s!AiUZ0Ws7G26RjHjBqQLMSuBbSLUn?e=aIZfl8

[Ahmedx]

and try this

[Ahmedx]

you must create a suitable model

[seefadeeb]

It should also filter expense type, the answer should be 1226 rather than 1612. Here it should also, remove filters on expense types fees and training which are not related to Shirley

[Ahmedx · Accepted solution]

pls try this