Power Pivot - Forecast Subtotal not totaling future values

[DAXRichArd]

Hello,

I'm posting here because I did not see a forum for Power Pivot.

I work with commercial aviation data and I am forcasting passengers. My method is to use a multiplier of 1% (or x 1.01).

Problem

My subtotal is not totalling all the forecasted numbers.

Definitions

Pax[Pax] is the expression of the sum of passengers

Calender [Date] is my date table.

Pax [Campus] is the airport identifier column. I am filtering for a specific airport.

My formula says: if a date is blank, multiply last years data ( Pax[Pax] ) times 1.01 (or 1%), else just display the data ( Pax[Pax] ).

This result is working well. But in the pivot table, the pivot table is not subtotalling the future values. It subtotals the current values but not the future values.

Formula

Helper Yr 1 IAH 1% :=

  VAR IAHYr1OnePct =
    CALCULATE (
        Pax[Pax] * 1.01,
        Pax[CAMPUS] = "IAH",
        SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
    )
RETURN
    CALCULATE (
        IF ( ISBLANK ( Pax[Pax] ), IAHYr1OnePct, Pax[Pax] ),
        Pax[CAMPUS] = "IAH"
    )

Please refer to the pic to see the results I'm getting.

Thank you in advance for your help!

[MFelix · Accepted solution]

Hi @DAXRichArd ,

Believe that the issue now is regarding the paraentheses but need to check the formula.

You can share it trough a onedrive, google drive, we transfer link, if there is sensitive information share it trough private message. Or can you make a mockupfile smaller.

Regards,

MFelix

[MFelix]

Hi @DAXRichArd ,

Although not having the file with sample information when using this type of formulas you need to add a SUMX for the subtotals something similar to this:

Helper Yr 1 IAH 1% :=
VAR IAHYr1OnePct =
    CALCULATE (
        Pax[Pax] * 1.01,
        Pax[CAMPUS] = "IAH",
        SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
    )
RETURN
    IF (
        HASONEVALUE ( Calendar[Date].[Month] ),
        CALCULATE (
            IF ( ISBLANK ( Pax[Pax] ), IAHYr1OnePct, Pax[Pax] ),
            Pax[CAMPUS] = "IAH",
            SUMX ( Calendar, IAHYr1OnePct )
        )
    )

Be aware that this may not result as expected since I made it by heart and not having a model if you can share a sample model I can adjust the measure in order to get the information matching your model.

But the mais point here is that the calculations are based on context and when you look at the subtotals it's picking up only the values where you have the pax  and the rest is considered as BLANK so not summed in your total.

Regards.

MFelix

[DAXRichArd]

@MFelix ,

Thanks for your reply.

The response I'm getting is:

"Semantic Error: A function 'SUMX' has been used in a True/False expression that is used as a table filter expression. This is not allowed. 

Do you have a means for me to share my file with you? It's 1.3MB in Excel 2016.

[MFelix · Accepted solution]

Hi @DAXRichArd ,

Believe that the issue now is regarding the paraentheses but need to check the formula.

You can share it trough a onedrive, google drive, we transfer link, if there is sensitive information share it trough private message. Or can you make a mockupfile smaller.

Regards,

MFelix

[DAXRichArd]

@MFelix 

Thx for your continued support. I sent you a private message with a link in Google Doc with the file. My office contact number is in the message as well.

Thx

[DAXRichArd]

@MFelix 

Received your message. Thx. Today is family day so I'll hack at it tomorrow. I'll update you as soon as possible. Enjoy your Sunday and have a great week ahead! RM

[DAXRichArd · Accepted solution]

@MFelix 

Big thank you. It worked. I hacked at adding SUMX but I didn't know what I was doing. I'm still trying to figure out why your solution works, but I think the bigger picture for me is that it's something to do with context transition and I'm still learning. No matter. I'll keep learning. Big thank you again. You rock! for everyone esle, below is the solution provided to me by @MFelix :

Original formula (problem was it did not subtotal the forecasted numbers).

Helper Yr 1 IAH 1% :=

VAR IAHYr1OnePct =

    CALCULATE (

        Pax[Pax] * 1.01,

        Pax[CAMPUS] = "IAH",

        SAMEPERIODLASTYEAR ( 'Calendar'[Date] )

    )

RETURN

    CALCULATE (

        IF ( ISBLANK ( Pax[Pax] ), IAHYr1OnePct, Pax[Pax] ),

        Pax[CAMPUS] = "IAH"

    )

SOLUTION:

Helper Yr 1 IAH 1% :=

SUMX (

    'Calendar',

    VAR IAHYr1OnePct =

        CALCULATE (

            Pax[Pax] * 1.01,

            Pax[CAMPUS] = "IAH",

            SAMEPERIODLASTYEAR ( 'Calendar'[Date] )

        )

    RETURN

        CALCULATE (

            IF ( ISBLANK ( Pax[Pax] ), IAHYr1OnePct, Pax[Pax] ),

            Pax[CAMPUS] = "IAH"

        )

)