How to Get Averages for Matrix Column Totals

[curtismob]

I have a matrix with monthly cost per sqft, for current and previous month.  I want all totals to be the average.  The row totals at the bottom appear to be working, but the column totals to the right are blank.  The Google drive link for the pbix is below, as well as the DAX for all related calculated columns and measures.

PBIX Sample link:

https://drive.google.com/file/d/1yQYI0MC8c86qqDrTTyb6XCG62RNRrNse/view?usp=sharing

_cDate Prev Month =
DATEADD('_tDates'[Date].[Date], -1, MONTH)

_cDate YYYYMM Prev =
FORMAT('_tDates'[_cDate Prev Month], "YYYYMM")

_mYYYYMM Selected =
IF (
ISFILTERED ( '_tDates'[YYYYMM] ),
FIRSTNONBLANK( '_tDates'[YYYYMM], 0),
FIRSTNONBLANK( '_tDates'[YYYYMM], 0)
)

_mYYYYMM Selected Prev =
IF (
ISFILTERED ( '_tDates'[_cDate YYYYMM Prev] ),
FIRSTNONBLANK( '_tDates'[_cDate YYYYMM Prev], 0),
FIRSTNONBLANK( '_tDates'[_cDate YYYYMM Prev], 0)
)

_mCost Month Curr =
CALCULATE(SUM(Costs[_cCostPerSQFT]),
FILTER(Costs,
Costs[YearMonth] = _tDates[_mYYYYMM Selected]))

_mCost Month Prev =
CALCULATE(SUM(Costs[_cCostPerSQFT]),
FILTER(Costs,
Costs[YearMonth] = _tDates[_mYYYYMM Selected Prev]))

_mAvg Cost Month Curr =
AVERAGEX(SUMMARIZE(Costs, Costs[Plan], Costs[YearMonth]),
Costs[_mCost Month Curr])

_mAvg Cost Month Prev =
AVERAGEX(SUMMARIZE(Costs, Costs[Plan], Costs[YearMonth]),
Costs[_mCost Month Prev])

Thanks in advance,

@curtismob 

[curtismob]

Hello, just curious if anyone has any ideas?

Thanks, @curtismob 

[Anonymous]

Hi @curtismob,

It seems like a common measure calculate with multiple level aggregations issue, I’d like to suggest you try to use following formulas if helps:

CurrCost=
VAR currDate =
    MAX ( '_tDates'[Date] )
VAR summary =
    GROUPBY (
        ALLSELECTED ( Costs ),
        Costs[Plan],
        Costs[YearMonth],
        "Cost", SUMX ( CURRENTGROUP (), [_cCostPerSQFT] )
    )
RETURN
    IF (
        ISFILTERED ( Costs[YearMonth] ),
        CALCULATE (
            SUM ( Costs[_cCostPerSQFT] ),
            FILTER (
                ALLSELECTED ( Costs ),
                Costs[YearMonth]
                    = YEAR ( currDate ) * 100
                        + MONTH ( currDate )
            ),
            VALUES ( Costs[Plan] )
        ),
        AVERAGEX ( summary, [Cost] )
    )

prevCost =
VAR currDate =
    MAX ( '_tDates'[Date] )
VAR prevDate =
    DATE ( YEAR ( currDate ), MONTH ( currDate ) - 1, DAY ( currDate ) )
VAR summary =
    GROUPBY (
        ALLSELECTED ( Costs ),
        Costs[Plan],
        Costs[YearMonth],
        "Cost",
            VAR currYM =
                MAX ( Costs[YearMonth] )
            VAR cDate =
                DATE ( LEFT ( currYM, 4 ), RIGHT ( currYM, 2 ) - 1, 1 )
            VAR pDate =
                DATE ( YEAR ( cDate ), MONTH ( cDate ) - 1, DAY ( cDate ) )
            RETURN
                CALCULATE (
                    SUM ( Costs[_cCostPerSQFT] ),
                    FILTER (
                        ALLSELECTED ( Costs ),
                        Costs[YearMonth]
                            = YEAR ( pDate ) * 100
                                + MONTH ( pDate )
                    ),
                    VALUES ( Costs[Plan] )
                )
    )
RETURN
    IF (
        ISFILTERED ( Costs[YearMonth] ),
        CALCULATE (
            SUM ( Costs[_cCostPerSQFT] ),
            FILTER (
                ALLSELECTED ( Costs ),
                Costs[YearMonth]
                    = YEAR ( prevDate ) * 100
                        + MONTH ( prevDate )
            ),
            VALUES ( Costs[Plan] )
        ),
        AVERAGEX ( summary, [Cost] )
    )

Measure Totals, The Final Word  

Regards,

Xiaoxin Sheng

[curtismob]

Thank you for the response.  I am curious if these measures were tested in the matrix.  I copy and pasted them to my copy of the pbix, CurrCost was valid, but the prevCost has the following error:

I added the CurrCost to the matrix and get the results below, which are not correct.

@curtismob