Forum Discussion

curtismob's avatar
curtismob
Icon for Helper IV rankHelper IV
3 years ago

How to Get Averages for Matrix Column Totals

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 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      curtismob
      Icon for Helper IV rankHelper IV
      3 years ago

      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