cancel
Showing results for
Did you mean:
Frequent Visitor

## Row Subtotals not calculating correctly for Forecast

Hello!

Can anybody help me with getting my subtotals to tie?! I'm basically taking a running avg % of total sales and applying that to projected sales to model out the expense. I use the same method in a lot of other places in the model to get to the blended forecast (WTD actuals + Remaining week projections) and it works just fine.

I've been pulling my hair out on this issue and would greatly appreciate whatever help could be offered. I can't share the pbx file publicly, but I provide a lot of screenshots below and will work to provide whatever else might be needed!

I should also mention that we use a retail calendar so many of the native date functions are of no use, unfortunately.

The "Day Range" referenced in the measures below is just a calculated column in my Date table where:

• >0 = Past Dates
• 0 = Current Day
• <0 = Future Dates

The image below is the current result produced:

• Columns 1/2 are a 6 week average of supplies and sales
• Column 3 = Column 2 / Column 1
• Column 4 = Column 3 * Column 4
• Column 7 = WTD Actuals + Rest of Week Projections

Column 7

``````Forecasted Top 11 \$ =

var ActualsDate = MAX('Daily Reporting'[Date])
var ProjectionsDate = LOOKUPVALUE('Daily Reporting'[Date],'Date'[Date],ActualsDate)
VAR CurWeek =  SELECTEDVALUE ( 'Date'[Week End Date] )
var DayRange = MAX('Date'[Day Range])
VAR FullWk =

CALCULATE(
[Actual Top 11 \$ - Total]
,ALL('Date')
,'Date'[Week End Date]=CurWeek)

// VAR Proj =

//     CALCULATE(
//         [Projected Top 11 - Total]
//         ,DayRange<=0)

RETURN

IF(
HASONEVALUE('Date'[Date])

,IF(
DayRange<=0

,[Actual Top 11 \$ - Total]
,[Projected Top 11 - Total])

,
CALCULATE(
[Actual Top 11 \$ - Total]
,('Date'[Day Range])<=0)
+
CALCULATE(
[Projected Top 11 - Total]
,('Date'[Day Range])>0)

)``````

Column 5

``````Projected Top 11 - Total =

[Trailing Avg - Supplies % - Total]*[Projected Total Sales]``````

Column 3

``````Trailing Avg - Supplies % - Total =

DIVIDE(
[Trailing Wkly Avg - Supplies \$ - Total]
,[Trailing Wkly Avg - Sales - Total])``````

Column 1

``````Trailing Wkly Avg - Supplies \$ - Total =

var MaxDate = LOOKUPVALUE('Daily Reporting'[Max Week End],'Date'[Date],MAX('Daily Reporting'[Max Date]))
VAR CurWeek =  SELECTEDVALUE ( 'Date'[Week End Date] )

VAR BegWeek = LOOKUPVALUE('Date'[Week End Date],'Date'[Date],SELECTEDVALUE('Date'[Week End Date]))
VAR EndWeek = LOOKUPVALUE('Date'[Week End Date],'Date'[Date],SELECTEDVALUE('Date'[Week End Date])-42)
VAR WeekRange = LOOKUPVALUE('Date'[4 Week Range],'Date'[Date],SELECTEDVALUE('Date'[Week End Date]))

VAR MaxWeek = LOOKUPVALUE('Date'[Max Week End],'Date'[Date],SELECTEDVALUE('Date'[Week End Date]))
VAR EndMaxWeek = LOOKUPVALUE('Date'[Max Week End],'Date'[Date],SELECTEDVALUE('Date'[Week End Date]))-42
VAR SelDay = SELECTEDVALUE('Date'[Day of Week Number - Sort])

VAR Expense =

IF(
HASONEVALUE('Date'[Date])

,ROUND(CALCULATE(
SUM('COGS & Top 11 Historical'[Amount])
,ALL('Date')
,'Date'[Week End Date]<BegWeek
&& 'Date'[Week End Date]>=EndWeek
&& 'Date'[Day of Week Number - Sort] = SelDay
,'COGS & Top 11 Historical'[AccountName] in
{"Supplies - ToGo"
,"Supplies - Paper"
,"Supplies - Office"
,"Supplies - Kitchen"
,"Supplies - General"
,"Supplies - Dishwashing"
,"Supplies - Dining Room"
,"Supplies - Bar"
,"Silverware"
,"Glassware"
,"Dishes"}
),0)

,ROUND(CALCULATE(
SUM('COGS & Top 11 Historical'[Amount])
,ALL('Date')
,'Date'[Week End Date]<BegWeek
&& 'Date'[Week End Date]>=EndWeek
,'COGS & Top 11 Historical'[AccountName] in
{"Supplies - ToGo"
,"Supplies - Paper"
,"Supplies - Office"
,"Supplies - Kitchen"
,"Supplies - General"
,"Supplies - Dishwashing"
,"Supplies - Dining Room"
,"Supplies - Bar"
,"Silverware"
,"Glassware"
,"Dishes"}
),0)
)

VAR MaxWeekExpense =

IF(
HASONEVALUE('Date'[Date])

,ROUND(CALCULATE(
SUM('COGS & Top 11 Historical'[Amount])
,ALL('Date')
,'Date'[Week End Date]<MaxWeek
&& 'Date'[Week End Date]>=EndMaxWeek
&& 'Date'[Day of Week Number - Sort] = SelDay
, 'COGS & Top 11 Historical'[AccountName] in
{"Supplies - ToGo"
,"Supplies - Paper"
,"Supplies - Office"
,"Supplies - Kitchen"
,"Supplies - General"
,"Supplies - Dishwashing"
,"Supplies - Dining Room"
,"Supplies - Bar"
,"Silverware"
,"Glassware"
,"Dishes"}
),0)

,ROUND(CALCULATE(
SUM('COGS & Top 11 Historical'[Amount])
,ALL('Date')
,'Date'[Week End Date]<MaxWeek
&& 'Date'[Week End Date]>=EndMaxWeek
,'COGS & Top 11 Historical'[AccountName] in
{"Supplies - ToGo"
,"Supplies - Paper"
,"Supplies - Office"
,"Supplies - Kitchen"
,"Supplies - General"
,"Supplies - Dishwashing"
,"Supplies - Dining Room"
,"Supplies - Bar"
,"Silverware"
,"Glassware"
,"Dishes"}
),0)
)

VAR WeeksCount =

CALCULATE(
DISTINCTCOUNTNOBLANK('COGS & Top 11 Historical'[Week Ended])
,ALL('Date')
,'Date'[Week End Date]<BegWeek && 'Date'[Week End Date]>=EndWeek
)

VAR MaxWeekCount =

CALCULATE(
DISTINCTCOUNTNOBLANK('COGS & Top 11 Historical'[Week Ended])
,ALL('Date')
,'Date'[Week End Date]<MaxWeek && 'Date'[Week End Date]>=EndMaxWeek
)

RETURN

IF(
MaxDate>CurWeek

,DIVIDE(
ROUND(Expense,0),WeeksCount)

,DIVIDE(
ROUND(MaxWeekExpense,0),MaxWeekCount)

)``````

Column 2

``````Trailing Wkly Avg - Sales - Total =

var MaxDate = LOOKUPVALUE('Daily Reporting'[Max Week End],'Date'[Date],MAX('Daily Reporting'[Max Date]))
VAR CurWeek =  SELECTEDVALUE ( 'Date'[Week End Date] )
VAR BegWeek = LOOKUPVALUE('Date'[Week End Date],'Date'[Date],SELECTEDVALUE('Date'[Week End Date]))
VAR EndWeek = LOOKUPVALUE('Date'[Week End Date],'Date'[Date],SELECTEDVALUE('Date'[Week End Date])-42)
VAR WeekRange = LOOKUPVALUE('Date'[4 Week Range],'Date'[Date],SELECTEDVALUE('Date'[Week End Date]))

VAR MaxWeek = LOOKUPVALUE('Date'[Max Week End],'Date'[Date],SELECTEDVALUE('Date'[Week End Date]))
VAR EndMaxWeek = LOOKUPVALUE('Date'[Max Week End],'Date'[Date],SELECTEDVALUE('Date'[Week End Date]))-42
VAR SelDay = SELECTEDVALUE('Date'[Day of Week Number - Sort])

VAR Total_Sales = SUM('Daily Reporting'[Total Sales])
VAR Week_Count = DISTINCTCOUNTNOBLANK('Date'[Week End Date])

VAR Expense =

IF(
HASONEVALUE('Date'[Date])

,CALCULATE(
SUM('Daily Reporting'[Total Sales])
,ALL('Date')
,'Date'[Week End Date]<BegWeek
&& 'Date'[Week End Date]>=EndWeek
&& 'Date'[Day of Week Number - Sort] = SelDay
)

,CALCULATE(
SUM('Daily Reporting'[Total Sales])
,ALL('Date')
,'Date'[Week End Date]<BegWeek
&& 'Date'[Week End Date]>=EndWeek
)
)

VAR WeeksCount =

CALCULATE(
DISTINCTCOUNTNOBLANK('Date'[Week End Date])
,ALL('Date')
,'Date'[Week End Date]<BegWeek
&& 'Date'[Week End Date]>=EndWeek
)

VAR MaxWeekExpense =

IF(
HASONEVALUE('Date'[Date])

,CALCULATE(
SUM('Daily Reporting'[Total Sales])
,ALL('Date')
,'Date'[Week End Date]<MaxWeek
&& 'Date'[Week End Date]>=EndMaxWeek
&& 'Date'[Day of Week Number - Sort] = SelDay
)

,CALCULATE(
SUM('Daily Reporting'[Total Sales])
,ALL('Date')
,'Date'[Week End Date]<MaxWeek
&& 'Date'[Week End Date]>=EndMaxWeek
)
)

VAR MaxWeekCount =

CALCULATE(
DISTINCTCOUNTNOBLANK('Date'[Week End Date])
,ALL('Date')
,'Date'[Week End Date]<MaxWeek
&& 'Date'[Week End Date]>=EndMaxWeek
)

return

IF(
MaxDate>CurWeek

,DIVIDE(
Expense,WeeksCount)

,DIVIDE(
MaxWeekExpense,MaxWeekCount)

)``````

THANKS!!!!

Jordan

1 ACCEPTED SOLUTION
Frequent Visitor

I was able to solve my problem thanks to an old post by @OwenAuger ! I figured the problem lied with the calculation steps of the trailing % and that I essentially needed to use something similiar to a SUMPRODUCT in Excel. So I went searching and found the post below:

https://community.powerbi.com/t5/Desktop/SumProduct-in-measure/td-p/370830

I then edited my forecasted top 11 formula (Column 7) to read as below and got the desired result!

``````Forecasted Top 11 \$ =

var ActualsDate = MAX('Daily Reporting'[Date])
var ProjectionsDate = LOOKUPVALUE('Daily Reporting'[Date],'Date'[Date],ActualsDate)
VAR CurWeek =  SELECTEDVALUE ( 'Date'[Week End Date] )
var DayRange = MAX('Date'[Day Range])
VAR FullWk =

CALCULATE(
[Actual Top 11 \$ - Total]
,ALL('Date')
,'Date'[Week End Date]=CurWeek)

RETURN

IF(
HASONEVALUE('Date'[Date])

,IF(
DayRange<=0

,[Actual Top 11 \$ - Total]
,[Projected Top 11 - Total])

,
CALCULATE(
[Actual Top 11 \$ - Total]
,('Date'[Day Range])<=0)
+

SUMX(

VALUES('Day Names'[Display Day])

,var Pct = CALCULATE([Trailing Avg - Supplies % - Total],('Date'[Day Range])>0)
var Amount = CALCULATE([Projected Total Sales],('Date'[Day Range])>0)

RETURN

Pct * Amount
)

)``````

2 REPLIES 2
Frequent Visitor

I was able to solve my problem thanks to an old post by @OwenAuger ! I figured the problem lied with the calculation steps of the trailing % and that I essentially needed to use something similiar to a SUMPRODUCT in Excel. So I went searching and found the post below:

https://community.powerbi.com/t5/Desktop/SumProduct-in-measure/td-p/370830

I then edited my forecasted top 11 formula (Column 7) to read as below and got the desired result!

``````Forecasted Top 11 \$ =

var ActualsDate = MAX('Daily Reporting'[Date])
var ProjectionsDate = LOOKUPVALUE('Daily Reporting'[Date],'Date'[Date],ActualsDate)
VAR CurWeek =  SELECTEDVALUE ( 'Date'[Week End Date] )
var DayRange = MAX('Date'[Day Range])
VAR FullWk =

CALCULATE(
[Actual Top 11 \$ - Total]
,ALL('Date')
,'Date'[Week End Date]=CurWeek)

RETURN

IF(
HASONEVALUE('Date'[Date])

,IF(
DayRange<=0

,[Actual Top 11 \$ - Total]
,[Projected Top 11 - Total])

,
CALCULATE(
[Actual Top 11 \$ - Total]
,('Date'[Day Range])<=0)
+

SUMX(

VALUES('Day Names'[Display Day])

,var Pct = CALCULATE([Trailing Avg - Supplies % - Total],('Date'[Day Range])>0)
var Amount = CALCULATE([Projected Total Sales],('Date'[Day Range])>0)

RETURN

Pct * Amount
)

)``````

Community Support

could you mark your answer as the solution? so that the others having same question can find this post quickly. Thanks.

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.