DAX custom Grand Total not behaving as expected

Question

I have a Measure which calculates a cumulative total:

CumulativeCount:=
VAR date1 = MAX( DimDate[Date] )
VAR date2 = MAX( FactTable[EndDate] )
RETURN
CALCULATE (
    SUM( FactTable[Count] ),
    DimDate[Date] <= date1,
    DimDate[Date] <= date2, 
    ALL( DimDate[Date] )
)

And another, actually used in the Pivot Table, which, when it's calculating the Grand Total, is supposed to add up the cumulative totals for each date:

CumulativeCountForPivot:=
IF (
    -- If calculating for one group
    COUNTROWS( VALUES( FactTable[Group] ) ) = 1,
    -- Do core logic
    [CumulativeCount],
    -- Else add up the results from each group
    SUMX(
        VALUES( FactTable[Group] ),
        [CumulativeCount]
    )
)

I don't understand why the Grand Total in the final column is 12, not 6.

Alexis Olson Alexis Olson · Accepted Answer · 2018-04-25T17:47:14

The reason is that the grand total is for GroupA and GroupB combined and there is a cumulative count of 12 on that date (6 for each group).

On 06/01/2017 there are no records for GroupA so the [CumulativeCount] measure is returning a blank, even though there are records before that date and the count would be 6. If you added a record for GroupA with a Count of 0 on 06/01/2017, then you would see 6 appear.

If you want a measure that only shows 6 on that date, then try something like this:

CountForPivot =
    VAR TempTable = SUMMARIZE(FactTable,
                        FactTable[Group],
                        FactTable[EndDate],
                        "Cumulative",
                            CALCULATE(SUM(FactTable[Count]),
                                FILTER(ALLEXCEPT(FactTable, FactTable[Group]),
                                    FactTable[EndDate] <= MAX(FactTable[EndDate])
                                )
                            )
                    )
    RETURN SUMX(TempTable, [Cumulative])

DAX custom Grand Total not behaving as expected

1 Answers