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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
dirty2k9
Regular Visitor

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

 

 

Capture.PNG

2 REPLIES 2
amitchandak
Super User
Super User

@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])

Thank you for the quick response. I tried both of your suggestions and both produce incorrect values. See the attached pic.

 

Capture(1).PNG

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.