Forum Discussion
Helper IIWorking with Budgets
Hey Everyone,
I have a query I cant work out in my head, I have a report im building for my department to track overtime spend, everytime an employee submits overtime its done on MS Form, this stores the data in a sharepoint list which I link to my employee table via a Many to One relationship, I also have a Date table linked to the Overtime date
What im trying to do is show Overtime Cost with remaning Budget in a Matrix visual, each employee has a £300 budget per month, meaning if the manager has 10 heads he would have £3000 per month, ive got this working if everyone does overtime but if everyone doesnt its not including the peoples budget as part of whats remaining as they have submitted any overtime. Here is is mock of the visual ive done in Excel just to remove peoples names for confidentiality as well as how i would like it to look
| Overtime Cost | Budget Remaining | |
| Head of Department Name | £6,912.86 | £11,087.11 |
| ManagerName1 | £4,484.41 | £6,315.56 |
| EmployeeName1 | £893.91 | £2,706.09 |
| EmployeeName2 | £2,502.94 | £1,097.06 |
| EmployeeName3 | £1,087.56 | £2,512.41 |
| ManagerName2 | £1,027.99 | £2,572.01 |
| EmployeeName1 | £1,027.99 | £2,572.01 |
| ManagerName3 | £1,400.46 | £2,199.54 |
| EmployeeName1 | £1,400.46 | £2,199.54 |
| Overtime Cost | Budget Remaining | |
| Head of Department Name | £6,912.86 | £46,500.00 |
| ManagerName1 (14 Heads) | £4,484.41 | £21,000.00 |
| EmployeeName1 | £893.91 | £2,706.09 |
| EmployeeName2 | £2,502.94 | £1,097.06 |
| EmployeeName3 | £1,087.56 | £2,512.41 |
| ManagerName2 (8 Heads) | £1,027.99 | £12,000.00 |
| EmployeeName1 | £1,027.99 | £2,572.01 |
| ManagerName3 (9 Heads) | £1,400.46 | £13,500.00 |
| EmployeeName1 | £1,400.46 | £2,199.54 |
Any help is appreciated, Thank you
Ok!
Monthly Budget =
VAR MonthsSelected = DISTINCTCOUNT('Date'[YearMonth])
VAR EmployeeCount =
IF(
ISINSCOPE(Employee[EmployeeName]),
1,
CALCULATE(
COUNTROWS(Employee),
ALL(Employee[EmployeeName])
)
)
RETURN
EmployeeCount * 300 * MonthsSelected
Budget Remaining =
VAR MonthsSelected = DISTINCTCOUNT('Date'[YearMonth])
VAR EmployeeCount =
IF(
ISINSCOPE(Employee[EmployeeName]),
1,
CALCULATE(
COUNTROWS(Employee),
ALL(Employee[EmployeeName])
)
)
VAR TotalBudget = EmployeeCount * 300 * MonthsSelected
VAR OvertimeSpend = [Overtime Cost]
RETURN
TotalBudget - OvertimeSpend
13 Replies
- Ashish_Mathur
Super User
Hi,
Please share some data to work with and show the expected result.
- Shai_Karmani
The issue is that your Budget measure is being filtered by the overtime fact table, so employees who haven't submitted any overtime get dropped from the totals at the manager and department levels.
The fix is to build the budget off the Employee dimension instead of the fact, so every employee in scope is counted regardless of whether they appear in the overtime list. Something like:
Budget = VAR MonthsInContext = DISTINCTCOUNT ( 'Date'[YearMonth] ) RETURN SUMX ( VALUES ( 'Employee'[EmployeeID] ), 300 * MonthsInContext ) Budget Remaining = [Budget] - [Overtime Cost]Swap 'Employee', 'Date', and the month column with your actual names. Because SUMX iterates the Employee table directly, employees who have not submitted any overtime still contribute their £300 per month to the manager and department totals.
If this helped, a thumbs up and accepted solution would be appreciated.
Best regards,
Shai Karmani - oussamahaimoud
Memorable Member
Hi AshleyJ17,
That will be easy if you share your exact table/column names so I can help you more. Instead, your relationship flows: Overtime List → Employee Table → Date Table. Which means that when an employee submits no overtime, they have no rows in the Overtime List, so they're invisible to your measure at the employee level, but their £300/month budget still needs to count.
Here's a step by step solution:
Step 1: Overtime Cost Measure
Overtime Cost =
SUM(OvertimeList[Cost])
Step 2: Monthly budget measure
This needs to calculate budget based on the Employee table, not the Overtime table:
Monthly Budget =
VAR MonthsSelected =
DISTINCTCOUNT('Date'[Year-Month]) -- or however your date table stores months
RETURN
COUNTROWS(
CALCULATETABLE(
'EmployeeTable',
ALLEXCEPT('EmployeeTable', 'EmployeeTable'[ManagerName], 'EmployeeTable'[EmployeeName])
)
) * 300 * MonthsSelected
Step 3: Budget remaining measure
Budget Remaining =
VAR MonthsSelected =
DISTINCTCOUNT('Date'[Year-Month])
VAR TotalBudget =
CALCULATE(
COUNTROWS('EmployeeTable'),
ALLEXCEPT(
'EmployeeTable',
'EmployeeTable'[ManagerName]
)
) * 300 * MonthsSelected
VAR OvertimeSpend =
CALCULATE(
SUM(OvertimeList[Cost]),
ALLEXCEPT(
'EmployeeTable',
'EmployeeTable'[ManagerName],
'EmployeeTable'[EmployeeName]
)
)
RETURN
TotalBudget - OvertimeSpend
Step 4: Matrix visual setup
- Rows: EmployeeTable[ManagerName] → EmployeeTable[EmployeeName]
- Columns: (empty)
- Values: [Overtime Cost], [Budget Remaining]
Remember that you should drag fields from your Employee table into rows, NOT from the overtime list. This ensures all employees appear regardless of whether they submitted overtime.
Hope this helps! Don't forget to accept as solution ✅ and give kudos👍in order to keep helping others.
Best regards,
Oussama (Data Consultant - Expert Fabric & Power BI)
- AshleyJ17
Hi Oussame,
Thank you for your reponse
Please see my Relationships, its a pretty straight forward model
Fact Table (Overtime) [DateId] --> Dimension Table (Date) [DateId]
Fact Table (Overtime) [EmployeeId] --> Dimension Table (Employee) [EmployeeId]
The fact table doesnt have OvertimeTime cost, only hours worked, this is becasue this doesnt contain employees salaries, to work out overtime cost we multiple the hours worked by the Average Hourly rate
v-echaithraCommunity Support
Hi AshleyJ17 ,
You could also approach this by creating a Budget measure that is based solely on the Employee dimension rather than the Overtime fact table.
For example:
Budget =
VAR HeadCount =
DISTINCTCOUNT(Employee[EmployeeId])
VAR Months =
DISTINCTCOUNT('Date'[YearMonth])
RETURN
HeadCount*300*Months
This ensures that all employees contribute to the budget calculation, even if they have not submitted any overtime records.
One thing to verify is that the Matrix rows are coming from the Employee dimension and not the Overtime fact table. If the visual is driven by the fact table, employees without overtime entries will not appear in the hierarchy.As an alternative, if the £300 monthly budget is fixed for all employees, you could consider creating a separate Budget table and calculate Budget Remaining as:
Budget Remaining =
SUM(Budget[Budget]) - [Overtime Cost]
This can be a cleaner and more scalable approach, especially if budget reporting requirements become more complex over time.Just to clarify, is the £300 budget fixed for every employee, or can it vary by employee or manager?
