- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Incorrect Grand Total in Forecast Measure
I give up! First off, thank you in advance for the help. I have been struggling with this problem for a couple of days now and know it's a context issue. However, I am new to Power BI and haven't had that Ah-Ha moment yet.
I have a Calendar table and Payroll table that had a one to many relationship on Date. I'm trying to create a measure that combines actual monthly payroll with projected monthly payroll. Projected monthly payroll is simply last month's payroll as it should remain constant for the remainder of the fiscal year. My goal is to plot actuals and forecasted running totals on one line chart and use what if scenarios to project future payroll expenses.
The problem I'm having is my grand total only consists of actuals. I've tried so many different functions in just about every variable and can't quite get it.
Any help is greatly appreciated.
Projected Payroll =
VAR currentMonth = CALCULATE(MAX('Payroll Details'[Fiscal Year & PP]), REMOVEFILTERS())
VAR payrollActuals = CALCULATE(SUM('Payroll Details'[Amount]), FILTER('Payroll Details', 'Payroll Details'[Fiscal Year & PP] < currentMonth))
VAR lastMonthsPayroll = CALCULATE(SUM('Payroll Details'[Amount]), 'Payroll Details'[Fiscal Year & PP] = currentMonth - 1, ALLSELECTED('Payroll Details'))
VAR projectedPayroll = IF(payrollActuals = BLANK() || MAX('Date'[Fiscal Year & PP]) = currentMonth, lastMonthsPayroll)
VAR result = payrollActuals + projectedPayroll
RETURN result
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@dirty2k9 , try to change return like
Sumx(Values(Date[Fiscal Month]), result)
or
create a new measure
Projected Payroll 1=
Sumx(Values(Date[Fiscal Month]), [Projected Payroll])
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview. ✨
️ November 12th-14th, 2024
Online Event
Register Here
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you for the quick response. I tried both of your suggestions and both produce incorrect values. See the attached pic.
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
05-28-2024 03:46 AM | |||
02-21-2024 08:15 AM | |||
07-18-2024 06:44 AM | |||
12-13-2023 09:18 AM | |||
10-26-2023 11:35 PM |
User | Count |
---|---|
128 | |
81 | |
59 | |
57 | |
44 |
User | Count |
---|---|
184 | |
111 | |
82 | |
66 | |
52 |