cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

Calculate Sum of Expenses by Manager

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!

1 ACCEPTED SOLUTION
Super User

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])``

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

Check out my latest demo report in the data story gallery.

Stand with Ukraine!

Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

4 REPLIES 4
Frequent Visitor

@ Thanks much for sharing your code!

Frequent Visitor

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?

Super User

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])``

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

Check out my latest demo report in the data story gallery.

Stand with Ukraine!

Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

Super User

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

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

Check out my latest demo report in the data story gallery.

Stand with Ukraine!

Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

Announcements

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors