Forecast Total Issue

[adsa]

Hi,

I seem to have an issue with the overall totals in my forecasting table. It doesn’t display the the correct total. I have followed the tutorials around this and reviewed other posts in the forum but can’t seem see what the issue is. It seems to exclude FY2022 figures completely in the overall total.

Forecast Calc = 
VAR Tot_1_Day_P_Rev =
    SUMX (
        CALCULATETABLE (
            'CTL CommPay',
            DATEADD ( Dates[Date], -1, DAY ),
            ALL ( 'CTL CommPay'[PaymentPeriod] )
        ),
        'CTL CommPay'[AmountGross]
    )
VAR Tot_3_Day_P_Rev =
    SUMX (
        CALCULATETABLE (
            'CTL CommPay',
            DATEADD ( Dates[Date], -3, DAY ),
            ALL ( 'CTL CommPay'[PaymentPeriod] )
        ),
        'CTL CommPay'[AmountGross]
    )
VAR Tot_7_Day_P_Rev =
    SUMX (
        CALCULATETABLE (
            'CTL CommPay',
            DATEADD ( Dates[Date], -7, DAY ),
            ALL ( 'CTL CommPay'[PaymentPeriod] )
        ),
        'CTL CommPay'[AmountGross]
    )
VAR Tot_28_Day_P_Rev =
    SUMX (
        CALCULATETABLE (
            'CTL CommPay',
            DATEADD ( Dates[Date], -28, DAY ),
            ALL ( 'CTL CommPay'[PaymentPeriod] )
        ),
        'CTL CommPay'[AmountGross]
    )
VAR Tot_1_Month_P_Rev =
    SUMX (
        CALCULATETABLE (
            'CTL CommPay',
            DATEADD ( Dates[Date], -1, MONTH ),
            ALL ( 'CTL CommPay'[PaymentPeriod] )
        ),
        'CTL CommPay'[AmountGross]
    )
VAR Tot_3_Month_P_Rev =
    SUMX (
        CALCULATETABLE (
            'CTL CommPay',
            DATEADD ( Dates[Date], -3, MONTH ),
            ALL ( 'CTL CommPay'[PaymentPeriod] )
        ),
        'CTL CommPay'[AmountGross]
    )
VAR Tot_6_Month_P_Rev =
    SUMX (
        CALCULATETABLE (
            'CTL CommPay',
            DATEADD ( Dates[Date], -6, MONTH ),
            ALL ( 'CTL CommPay'[PaymentPeriod] )
        ),
        'CTL CommPay'[AmountGross]
    )
VAR Tot_1_Year_P_Rev =
    SUMX (
        CALCULATETABLE (
            'CTL CommPay',
            DATEADD ( Dates[Date], -1, YEAR ),
            ALL ( 'CTL CommPay'[PaymentPeriod] )
        ),
        'CTL CommPay'[AmountGross]
    )
RETURN
    ( Tot_1_Day_P_Rev + Tot_3_Day_P_Rev + Tot_7_Day_P_Rev + Tot_28_Day_P_Rev + Tot_1_Month_P_Rev + Tot_3_Month_P_Rev + Tot_6_Month_P_Rev + Tot_1_Year_P_Rev )
        / (
            IF ( ISBLANK ( Tot_1_Day_P_Rev ), 0, 1 )
                + IF ( ISBLANK ( Tot_3_Day_P_Rev ), 0, 1 )
                + IF ( ISBLANK ( Tot_7_Day_P_Rev ), 0, 1 )
                + IF ( ISBLANK ( Tot_28_Day_P_Rev ), 0, 1 )
                + IF ( ISBLANK ( Tot_1_Month_P_Rev ), 0, 1 )
                + IF ( ISBLANK ( Tot_3_Month_P_Rev ), 0, 1 )
                + IF ( ISBLANK ( Tot_6_Month_P_Rev ), 0, 1 )
                + IF ( ISBLANK ( Tot_1_Year_P_Rev ), 0, 1 )
        )

Summarized Forecast =
SUMX (
SUMMARIZE ( Dates, Dates[Date], “Summ Forecast”, [Forecast Calc] ),
[Summ Forecast]
)

Forecast =
SWITCH (
TRUE (),
ISBLANK ( [Total Gross CommPay RR] ) = TRUE (), [Summarized Forecast],
TRUE (), [Forecast Calc]
)

Total Gross CommPay RR = 
SUM ( 'CTL CommPay'[AmountGross] )

Thanks

[EnterpriseDNA]

Hi @adsa, I noticed you didn’t provide a PBIX file. Providing one will help users and experts find a solution to your inquiry faster and better.

A perfect initial question includes all of the following:

• Your current work-in-progress PBIX file
• Your underlying data file (to allow us to go into Power Query if necessary to transform your data and/or data model – often DAX questions really end up being data modeling solutions)

Check out this thread on Tools and Techniques for Providing PBIX Files with Your Forum Questions

Not completing your data may sometimes cause delay in getting an answer.

[adsa]

Attached PBIX & Excel Sample Files.

Incorrect overall grand total

Forecast.pbix (1.8 MB)
Sample Data.xlsx (2.9 MB)

[Melissa]

Hi @adsa,

Q: In your sample you are bringing in monthly data, is that also the case in your production model?
If so, please be aware of the fact that you can’t shift over a lower granularity without an additional allocation measure.

This affects your Avg calc so I removed that from FC Calc v2, below. Because it’s quite an extensive formula already I created a separate display measure where I correct the total by iterating over the Full FY and Month Name, when the Month Name isn’t INSCOPE.

Here’s your sample file.
Forecast.pbix (1.8 MB)

You can find more information and links to relevant content on fixing totals here:

Fix Incorrect Totals

When using a measure column in a visual, it is not uncommon in Power BI to find that the totals are incorrect. The Fix Incorrect Totals DAX pattern can be used to correct this issue. When a DAX measure is providing the correct detail value it often, unfortunately, provides an incorrect value for the total row. This is due to there being no evaluation context for the total row. For example, when comparing the [Total Sales] to the [Sales LY] to find the minimum for a customer using the simple DAX…

I hope this is helpful.

[adsa]

@Melissa Thank you so much for this.

Took me a little longer to digest what you have done but can finally understand it!