Calculate Sum of Expenses by Manager

[bnlguy1493]

Hi folks,

I'm looking to calculate the sum of expenses (Ex. VAT Cost) by line managers as well as each individual travel type. I have two tables: The first "Expense" table contains the travel expense including and excluding travel costs by each colleague. The second is a "name" table containing the names of colleagues and line managers. I have constructed a hierarchy path and a corresponding matrix table showing how much each colleague has spent and the matrix is also showing how much money (Ex. VAT Cost) is spent by each manager. However, you can see that the matrix is also displaying the expenses of the employees. I have tried multiple ways, but I can't figure out how to only display the names of the managers alone. As you can see from the matrix, there are four managers -- "Samira Mishra, Darien Jacobse, Irene Monet and Hildr Tennyson." I tried filtering using the "Is Manager?" column, but the matrix shows the sum of expenses for only that manager, without including the expenses of their subordinates. If I separately calcualate the group expenses (Ex. VAT Cost) using a calculate and sum command (in a calculated column), I get the correct expenses by each manager, but incorrect when i filter by travel type. 

Matrix: 

Name Table: 

Colleague ID	Colleague Name	Line Manager ID	Path	Level1ID	Level2ID	Level3ID	Level4ID	Level1Name	Level2Name	Level3Name	Level4Name	Is Manager?
232072	Irene Monet	201854	201854|232072	201854	232072			Samira Mishra	Irene Monet			TRUE
264976	Timon Matthews	232072	201854|232072|264976	201854	232072	264976		Samira Mishra	Irene Monet	Timon Matthews		FALSE
A10637	Mohini Patel	232072	201854|232072|A10637	201854	232072	A10637		Samira Mishra	Irene Monet	Mohini Patel		FALSE
299333	Vasia Bellincioni	3783	201854|281247|3783|299333	201854	281247	3783	299333	Samira Mishra	Darien Jacobse	Hildr Tennyson	Vasia Bellincioni	FALSE
295930	Amilcar Adam	3783	201854|281247|3783|295930	201854	281247	3783	295930	Samira Mishra	Darien Jacobse	Hildr Tennyson	Amilcar Adam	FALSE
D18660	Dileep Ibrahimovic	281247	201854|281247|D18660	201854	281247	D18660		Samira Mishra	Darien Jacobse	Dileep Ibrahimovic		FALSE
299391	Rosalee Jacques	281247	201854|281247|299391	201854	281247	299391		Samira Mishra	Darien Jacobse	Rosalee Jacques		FALSE
3783	Hildr Tennyson	281247	201854|281247|3783	201854	281247	3783		Samira Mishra	Darien Jacobse	Hildr Tennyson		TRUE
235419	Emanuel Hardwick	281247	201854|281247|235419	201854	281247	235419		Samira Mishra	Darien Jacobse	Emanuel Hardwick		FALSE
7789	Emely Bentley	281247	201854|281247|7789	201854	281247	7789		Samira Mishra	Darien Jacobse	Emely Bentley		FALSE
281247	Darien Jacobse	201854	201854|281247	201854	281247			Samira Mishra	Darien Jacobse			TRUE
289162	Chander Kaur	3783	201854|281247|3783|289162	201854	281247	3783	289162	Samira Mishra	Darien Jacobse	Hildr Tennyson	Chander Kaur	FALSE
201854	Samira Mishra	201854	201854	201854				Samira Mishra				TRUE
241708	Dervla Jarrett	232072	201854|232072|241708	201854	232072	241708		Samira Mishra	Irene Monet	Dervla Jarrett		FALSE

Expense Table: (Partial)

Name Table: 

Colleague ID	Colleague Name	Line Manager ID	Path	Level1ID	Level2ID	Level3ID	Level4ID	Level1Name	Level2Name	Level3Name	Level4Name	Is Manager?
232072	Kiran Sharma	201854	201854|232072	201854	232072			Xi Fung	Kiran Sharma			TRUE
264976	Blunt Smith	232072	201854|232072|264976	201854	232072	264976		Xi Fung	Kiran Sharma	Timon Matthews		FALSE
A10637	Mohini Patel	232072	201854|232072|A10637	201854	232072	A10637		Xi Fung	Kiran Sharma	Mohini Patel		FALSE
299333	Suresh Kaladi	3783	201854|281247|3783|299333	201854	281247	3783	299333	Xi Fung	Smith Wesson	Hildr Tennyson	Vasia Bellincioni	FALSE
295930	Ameen Khan	3783	201854|281247|3783|295930	201854	281247	3783	295930	Xi Fung	Smith Wesson	Hildr Tennyson	Amilcar Adam	FALSE
D18660	Clark Sasson	281247	201854|281247|D18660	201854	281247	D18660		Xi Fung	Smith Wesson	Dileep Ibrahimovic		FALSE
299391	Rosafeld Jacqueline	281247	201854|281247|299391	201854	281247	299391		Xi Fung	Smith Wesson	Rosalee Jacques		FALSE
3783	Blessen George	281247	201854|281247|3783	201854	281247	3783		Xi Fung	Smith Wesson	Hildr Tennyson		TRUE
235419	June Sun	281247	201854|281247|235419	201854	281247	235419		Xi Fung	Smith Wesson	Emanuel Hardwick		FALSE
7789	Bently Royce	281247	201854|281247|7789	201854	281247	7789		Xi Fung	Smith Wesson	Emely Bentley		FALSE
281247	Smith Wesson	201854	201854|281247	201854	281247			Xi Fung	Darien Jacobse			TRUE
289162	Chand Persh	3783	201854|281247|3783|289162	201854	281247	3783	289162	Xi Fung	Smith Wesson	Hildr Tennyson	Chander Kaur	FALSE
201854	Xi Fung	201854	201854	201854				Xi Fung				TRUE
241708	Ghulam Azad	232072	201854|232072|241708	201854	232072	241708		Samira Mishra	Irene Monet	Dervla Jarrett		FALSE

Expense Table: (Partial)

First Name	Middle Initial	Last Name	Inc. VAT Cost	Ex. VAT Cost	VAT Cost	Full Name	Type of Travel
Smith	M.	Wesson	£885	£738	147	Smith Wesson	Flight
Kiran	K.	Sharma	£71	£59	12	Kiran Sharma	Personal Car
Blessen		George	£94	£78	16	Blessen George	Business Car
Blunt	L.	Smith	£99	£83	16	Blunt Smith	Business Car
Jun		Sun	£70	£58	12	June Sun	Personal Car
Blessen		George	£598	£498	100	Blessen George	Hotel
Blessen		George	£40	£33	7	Blessen George	Personal Car
Xi		Fung	£614	£512	102	Samira Mishra	Flight
Clark	D.	Sasson	£55	£46	9	Clark Sasson	Personal Car

Just to summarize again, I want to display the expenses by each manager, taking into account the expenses by their subordinates as well. Thanks for the help!

[bnlguy1493]

@ Thanks much for sharing your code!

[bnlguy1493]

Hi @ERD  thanks! Yes, that is exactly what I want to achieve -- I tried to filter by the "Is Manager?" True/False filter but it was throwing up incorrect values. How did you do that? 

[ERD · Accepted solution]

@bnlguy1493 ,

Measure:

__amt = 
VAR amt = SUM ( Expense[Ex. VAT Cost] )
VAR nameInScope =
    SWITCH (
        TRUE (),
        ISINSCOPE ( 'Name'[Level4Name] ), SELECTEDVALUE ( 'Name'[Level4Name] ),
        ISINSCOPE ( 'Name'[Level3Name] ), SELECTEDVALUE ( 'Name'[Level3Name] ),
        ISINSCOPE ( 'Name'[Level2Name] ), SELECTEDVALUE ( 'Name'[Level2Name] ),
        SELECTEDVALUE ( 'Name'[Level1Name] )
    )
VAR isMgr =
    CALCULATE (
        SELECTEDVALUE ( 'Name'[Is Manager?] ),
        'Name'[Colleague Name] = nameInScope
    )
RETURN
    IF ( isMgr, amt )

Columns:

Level1Name = LOOKUPVALUE('Name'[Colleague Name], 'Name'[Colleague ID], PATHITEM('Name'[Path],1,TEXT))

Next ones with the same pattern:

Level2Name = IF( PATHLENGTH('Name'[Path]) >=2, LOOKUPVALUE('Name'[Colleague Name], 'Name'[Colleague ID], PATHITEM('Name'[Path], 2, TEXT)), 'Name'[Level1Name])

NodeDepth = PATHLENGTH('Name'[Path])

Path = PATH('Name'[Colleague ID], 'Name'[Line Manager ID])

[ERD]

Hi @bnlguy1493 , is this the result you want to achieve?