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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
bnlguy1493
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 IDColleague NameLine Manager IDPathLevel1IDLevel2IDLevel3IDLevel4IDLevel1NameLevel2NameLevel3NameLevel4NameIs Manager?  
232072Kiran Sharma201854201854|232072201854232072  Xi Fung Kiran Sharma  TRUE  
264976Blunt Smith232072201854|232072|264976201854232072264976 Xi Fung Kiran SharmaTimon Matthews FALSE  
A10637Mohini Patel232072201854|232072|A10637201854232072A10637 Xi Fung Kiran SharmaMohini Patel FALSE  
299333Suresh Kaladi3783201854|281247|3783|2993332018542812473783299333Xi FungSmith WessonHildr TennysonVasia BellincioniFALSE  
295930Ameen Khan3783201854|281247|3783|2959302018542812473783295930Xi FungSmith WessonHildr TennysonAmilcar AdamFALSE  
D18660Clark Sasson281247201854|281247|D18660201854281247D18660 Xi FungSmith WessonDileep Ibrahimovic FALSE  
299391Rosafeld Jacqueline281247201854|281247|299391201854281247299391 Xi FungSmith WessonRosalee Jacques FALSE  
3783Blessen George281247201854|281247|37832018542812473783 Xi FungSmith WessonHildr Tennyson TRUE  
235419June Sun281247201854|281247|235419201854281247235419 Xi FungSmith WessonEmanuel Hardwick FALSE  
7789Bently Royce281247201854|281247|77892018542812477789 Xi FungSmith WessonEmely Bentley FALSE  
281247Smith Wesson201854201854|281247201854281247  Xi FungDarien Jacobse  TRUE  
289162Chand Persh3783201854|281247|3783|2891622018542812473783289162Xi FungSmith WessonHildr TennysonChander KaurFALSE  
201854Xi Fung201854201854201854   Xi Fung   TRUE  
241708Ghulam Azad232072201854|232072|241708201854232072241708 Samira MishraIrene MonetDervla Jarrett FALSE  

 

Expense Table: (Partial)

 

First NameMiddle InitialLast NameInc. VAT CostEx. VAT CostVAT CostFull NameType of Travel
SmithM.Wesson£885£738147Smith WessonFlight
KiranK.Sharma£71£5912Kiran SharmaPersonal Car
Blessen George£94£7816Blessen GeorgeBusiness Car
BluntL.Smith£99£8316Blunt SmithBusiness Car
Jun Sun£70£5812June SunPersonal Car
Blessen George£598£498100Blessen GeorgeHotel
Blessen George£40£337Blessen GeorgePersonal Car
Xi Fung£614£512102Samira MishraFlight
ClarkD.Sasson£55£469Clark SassonPersonal Car

 

1 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:

OwenAuger_0-1695864194311.png

 

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

View solution in original post

6 REPLIES 6
OwenAuger
Super User
Super User

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:

OwenAuger_0-1695801583259.png

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

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:

image.png

After filtering: 

image.png

 

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:

OwenAuger_0-1695864194311.png

 

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

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

amitchandak
Super User
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...

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Top Solution Authors