Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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 -- "Xi Fung, Smith Wesson, Kiran Sharma, Blessen George." 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.

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

1 ACCEPTED SOLUTION
Super User

Hi again @bnlguy1493

Thanks for the feedback!

Yes, you're right! I had made an accidental error by including only descendants that were within the overall filter context of the visual by using ALLSELECTED ().

This meant that when Is Manager = TRUE, any non-managers were excluded from the manager totals.

I have fixed this in the attached version. Also, in case it's useful, I created a third Calculation Item "Include Descendants and Display Only Managers" that limits the visual to just managers without needing an additional slicer.

I also simplified the code a little bit.

Here is a snapshot of the report page & the updated Calculation Group DAX script:

``````-------------------------------------------
-- Calculation Group: 'Hierarchy Filtering'
-------------------------------------------
CALCULATIONGROUP 'Hierarchy Filtering'[Hierarchy Filtering Rule]

CALCULATIONITEM "Default" = SELECTEDMEASURE ()
FormatString = SELECTEDMEASUREFORMATSTRING ()

CALCULATIONITEM "Include Descendants" =
VAR CurrentColleagues =
VALUES ( 'name'[Colleague ID] )
VAR DescendantsList =
SELECTCOLUMNS (
GENERATE (
CurrentColleagues,
VAR  CurrentColleagueID = 'name'[Colleague ID]
RETURN
SELECTCOLUMNS (
CALCULATETABLE (
SUMMARIZE ( 'name', 'name'[Colleague ID], 'name'[Path] ),
PATHCONTAINS ( 'name'[Path], CurrentColleagueID ),
REMOVEFILTERS ( 'name' )
),
"@DescendantID", 'name'[Colleague ID]
)
),
"@DescendantID", [@DescendantID] -- this has lineage to 'name'[Colleague ID]
)
RETURN
CALCULATE (
SELECTEDMEASURE (),
REMOVEFILTERS ( 'name' ),
DescendantsList
)
FormatString = SELECTEDMEASUREFORMATSTRING ()

CALCULATIONITEM "Include Descendants and Display Only Managers" =
VAR CurrentColleagues =
CALCULATETABLE (
VALUES ( 'name'[Colleague ID] ),
KEEPFILTERS ( 'name'[Is Manager?] ) -- Just include Managers
)
VAR DescendantsList =
SELECTCOLUMNS (
GENERATE (
CurrentColleagues,
VAR  CurrentColleagueID = 'name'[Colleague ID]
RETURN
SELECTCOLUMNS (
CALCULATETABLE (
SUMMARIZE ( 'name', 'name'[Colleague ID], 'name'[Path] ),
PATHCONTAINS ( 'name'[Path], CurrentColleagueID ),
REMOVEFILTERS ( 'name' )
),
"@DescendantID", 'name'[Colleague ID]
)
),
"@DescendantID", [@DescendantID] -- this has lineage to 'name'[Colleague ID]
)
RETURN
CALCULATE (
SELECTEDMEASURE (),
REMOVEFILTERS ( 'name' ),
DescendantsList
)
FormatString = SELECTEDMEASUREFORMATSTRING ()``````

Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
6 REPLIES 6
Super User

Adding to the other replies:

I have attached a PBIX with some ideas. 🙂

I took the data you posted an invented some myself.

1. You can create a Calculation Group that includes the logic for including "Descendants" when calculating measures (requires Tabular Editor 2 or 3).

• Default just returns the normal value.
• Include Descendants uses the Path column to retrieve the descendants of each visible Colleague ID, and include them along with the visible Colleague ID when computing measures.

Here is the DAX script:

``````-------------------------------------------
-- Calculation Group: 'Hierarchy Filtering'
-------------------------------------------
CALCULATIONGROUP 'Hierarchy Filtering'[Hierarchy Filtering Rule]

CALCULATIONITEM "Default" = SELECTEDMEASURE ()
FormatString = SELECTEDMEASUREFORMATSTRING ()

CALCULATIONITEM "Include Descendants" =
VAR CurrentColleagues =
SELECTCOLUMNS (
VALUES ( 'name'[Colleague ID] ),
"@ColleagueID", 'name'[Colleague ID]
)
VAR AllselectedColleagues =
CALCULATETABLE (
VALUES ( 'name'[Colleague ID] ),
ALLSELECTED ()
)
VAR DescendantsList =
SELECTCOLUMNS (
GENERATE (
CurrentColleagues,
VAR  CurrentColleagueID = [@ColleagueID]
RETURN
SELECTCOLUMNS (
CALCULATETABLE (
SUMMARIZE ( 'name', 'name'[Colleague ID], 'name'[Path] ),
AllselectedColleagues,
PATHCONTAINS ( 'name'[Path], CurrentColleagueID ),
REMOVEFILTERS ( 'name' )
),
"@DescendantID", 'name'[Colleague ID]
)
),
"@DescendantID", [@DescendantID] -- this still has lineage to 'name'[Colleague ID]
)
RETURN
CALCULATE (
SELECTEDMEASURE (),
ALL ( 'name' ),
DescendantsList
)
FormatString = SELECTEDMEASUREFORMATSTRING ()``````

2. Then you can create a report page with

• A slicer to filter on the Calculation Group to include/exclude descendants
• A slicer to filter Is Manager?

There may be some adjustments needed to the display of the report, but here is an example:

You may also want to look at this article for other ideas:

https://www.daxpatterns.com/parent-child-hierarchies/

Regards

Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Frequent Visitor

Hi @OwenAuger , Thanks so much for your response. Really appreciate the effort you put in preparing the solution. My table that I had posted is similar to what you have prepared, and the problem for me arises when I filter on "Is Manager." That is, I want to display the sum of the expenses under a line manager without displaying the employee' names. Currently, when I filter by "Is Manager" = True, I am only getting a display of the line manager's own expenses, without accounting for the people below them. If I filter by "Is manager", you can see how their expenses are different. Essentially, I would say that the descriptive table that you have attached is most comprehensive, but I was wondering if there was a way to report the manager sub-totals alone? I believe the logic lies in using the Path column to retrieve the descendants of each visible Colleague ID, and include them along with the visible Colleague ID when computing, but not show the "descendants" in the table. I tried to play around with the code provided by you, but am proving unsuccessful in hiding the descendants. If you have an idea in mind, that would be great --- thanks again for your generous support!

Before filtering:

After filtering:

Super User

Hi again @bnlguy1493

Thanks for the feedback!

Yes, you're right! I had made an accidental error by including only descendants that were within the overall filter context of the visual by using ALLSELECTED ().

This meant that when Is Manager = TRUE, any non-managers were excluded from the manager totals.

I have fixed this in the attached version. Also, in case it's useful, I created a third Calculation Item "Include Descendants and Display Only Managers" that limits the visual to just managers without needing an additional slicer.

I also simplified the code a little bit.

Here is a snapshot of the report page & the updated Calculation Group DAX script:

``````-------------------------------------------
-- Calculation Group: 'Hierarchy Filtering'
-------------------------------------------
CALCULATIONGROUP 'Hierarchy Filtering'[Hierarchy Filtering Rule]

CALCULATIONITEM "Default" = SELECTEDMEASURE ()
FormatString = SELECTEDMEASUREFORMATSTRING ()

CALCULATIONITEM "Include Descendants" =
VAR CurrentColleagues =
VALUES ( 'name'[Colleague ID] )
VAR DescendantsList =
SELECTCOLUMNS (
GENERATE (
CurrentColleagues,
VAR  CurrentColleagueID = 'name'[Colleague ID]
RETURN
SELECTCOLUMNS (
CALCULATETABLE (
SUMMARIZE ( 'name', 'name'[Colleague ID], 'name'[Path] ),
PATHCONTAINS ( 'name'[Path], CurrentColleagueID ),
REMOVEFILTERS ( 'name' )
),
"@DescendantID", 'name'[Colleague ID]
)
),
"@DescendantID", [@DescendantID] -- this has lineage to 'name'[Colleague ID]
)
RETURN
CALCULATE (
SELECTEDMEASURE (),
REMOVEFILTERS ( 'name' ),
DescendantsList
)
FormatString = SELECTEDMEASUREFORMATSTRING ()

CALCULATIONITEM "Include Descendants and Display Only Managers" =
VAR CurrentColleagues =
CALCULATETABLE (
VALUES ( 'name'[Colleague ID] ),
KEEPFILTERS ( 'name'[Is Manager?] ) -- Just include Managers
)
VAR DescendantsList =
SELECTCOLUMNS (
GENERATE (
CurrentColleagues,
VAR  CurrentColleagueID = 'name'[Colleague ID]
RETURN
SELECTCOLUMNS (
CALCULATETABLE (
SUMMARIZE ( 'name', 'name'[Colleague ID], 'name'[Path] ),
PATHCONTAINS ( 'name'[Path], CurrentColleagueID ),
REMOVEFILTERS ( 'name' )
),
"@DescendantID", 'name'[Colleague ID]
)
),
"@DescendantID", [@DescendantID] -- this has lineage to 'name'[Colleague ID]
)
RETURN
CALCULATE (
SELECTEDMEASURE (),
REMOVEFILTERS ( 'name' ),
DescendantsList
)
FormatString = SELECTEDMEASUREFORMATSTRING ()``````

Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Frequent Visitor

Perfect, you're a life saver, @OwenAuger  thanks so much for your solution!

Super User

@bnlguy1493 , In this case when you use those level 1 column/s then only you will data rolled up.

If you need a manager like employee then we need change the measure logic

refer if these can help

https://www.sqlbi.com/articles/clever-hierarchy-handling-in-dax/

https://radacad.com/parsing-organizational-hierarchy-or-chart-of-accounts-in-power-bi-with-parent-ch...

Frequent Visitor

Hi @amitchandak thanks much for your tip and sharing the links. Am very glad to have found this community 🙂

## Helpful resources

Announcements

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors