Row Subtotals not calculating correctly for Forecast

[jordancole0517]

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

[jordancole0517 · Accepted solution]

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
    )
        

)

[v-xiaotang]

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.