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 -- "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

[OwenAuger]

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).

• 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

[bnlguy1493]

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: 

[OwenAuger · Accepted 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 ()

[bnlguy1493]

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

[amitchandak]

@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...

[bnlguy1493]

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