Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 |
Solved! Go to Solution.
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 ()
Hi @bnlguy1493
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).
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
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
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:
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 ()
@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/
Hi @amitchandak thanks much for your tip and sharing the links. Am very glad to have found this community 🙂
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
104 | |
98 | |
98 | |
40 | |
38 |
User | Count |
---|---|
151 | |
122 | |
78 | |
73 | |
67 |