PowerPivot - DAX - Budget Analysis - Grand Total not correct

Question

I am new to using PowerPivot and DAX, and I am looking for help on a common issue that I couldn’t solve so far --> comparing actual data/facts to budgets and Grand Total mismatching sum of lines. This is the model This is how the budget data looks like The actual data (for example TY TW Units) comes from the Booking Details table, which has similar columns to TicketTypeId, ResourceTypeId and DateId. This is the Booking Details table. It does not have a DateId column, but the linked TripId column has a link to Dates in its table.

We have a pivot table set up across the resource types hierarchy and the level 1 of the ticket type hierarchy.
This is the formula for the variance:

TY TW Units v Budget Units TY TW $ :=
CALCULATE ( 'Booking Details'[TY TW UNITS] - [Budget Units TY TW] )

What we want to achieve is:
1) Do not compute the variance (TY TW UNITS v BUDGET UNITS TY TW $ Var) if there is no budget
2) Grand Total should be computed as a sum of the output of the variance.
This last point is the trickiest, because I only managed to get to this result. The totals are not correct, because the underlying data is not filtered, and I do not know how to do that in this case.
The update formula for variance I used in this case:

TY TW Units v Budget Units TY TW $ :=
CALCULATE (
    IF (
        ISBLANK ( [Budget Units TY TW] ),
        BLANK (),
        'Booking Details'[TY TW UNITS] - [Budget Units TY TW]
    )
)

The data has null values in booking details for either one of resource or ticket type (not in all cases, but I feel it’s worth mentioning), but there is always a date id.
Any help is greatly appreciated.
Thank you.
P.S.
This is the formula I have in place for TY TW Budget Units:

Budget Units TY TW :=
CALCULATE (
    IF (
        Parameters[Filter Calendar Type] = "Fiscal",
        CALCULATE (
            SUM ( 'Budgets'[Vehicles] ) + SUM ( 'Budgets'[Passengers] ),
            Dates[Fiscal Year] = VALUES ( Parameters[Current Fiscal Year] ),
            Dates[Fiscal Week] = VALUES ( Parameters[Current Fiscal Week] )
        ),
        CALCULATE (
            SUM ( 'Budgets'[Vehicles] ) + SUM ( 'Budgets'[Passengers] ),
            Dates[Calendar Year] = VALUES ( Parameters[Current Calendar Year] ),
            Dates[Calendar Week] = VALUES ( Parameters[Current Calendar Week] )
        )
    )
)

This is the formula for TY TW UNITS:

TY TW UNITS :=
IF (
    Parameters[Filter Calendar Type] = "Fiscal",
    CALCULATE (
        [TRAFFIC UNITS],
        Dates[Fiscal Year] = VALUES ( Parameters[Current Fiscal Year] ),
        Dates[Fiscal Week] = VALUES ( Parameters[Current Fiscal Week] )
    ),
    CALCULATE (
        [TRAFFIC UNITS],
        Dates[Calendar Year] = VALUES ( Parameters[Current Calendar Year] ),
        Dates[Calendar Week] = VALUES ( Parameters[Current Calendar Week] )
    )
)

This relies on the traffic units, and the formula for that is:

TRAFFIC UNITS :=
CALCULATE ( SUM ( [UnitCount] ), 'Booking Details'[Checked In] = TRUE () )

Iulian Olteanu Iulian Olteanu · Accepted Answer · 2017-05-24T08:14:58

Solved this with an additional measure, that was basically the difference.

Diff TY TW Units v Budget Units TY TW:=SUMX (
    FILTER ( 'Booking Details', ISBLANK ( [Budget Units TY TW] ) ),
     'Booking Details'[TY TW UNITS]
)

This is the "old" calculation that was not computing correctly.

TY TW Units v Budget Units TY TW var:=CALCULATE (
    'Booking Details'[TY TW UNITS] - [Budget Units TY TW],
    FILTER (
        'Budgets',
        (
            CALCULATE (
                SUM ( 'Budgets'[Passengers] ) + SUM ( 'Budgets'[Vehicles] )
            )
                > 0
        )
    )
)

And these 2 come together in the actual measure which is shown to the user.

TY TW Units v Budget Units TY TW $:=CALCULATE(
    [TY TW Units v Budget Units TY TW var] + [Diff TY TW Units v Budget Units TY TW]
)

PowerPivot - DAX - Budget Analysis - Grand Total not correct

1 Answers