cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jordancole0517
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

 

jordancole0517_0-1625706644947.png

 

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

)

 

 

 

View solution in original post

2 REPLIES 2
jordancole0517
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
    )
        

)

 

 

 

View solution in original post

Hi @jordancole0517 

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.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors