Max Monthly Sales using MAXX TOPN Shows incorrect total

[SR_BI]

I have a matrix with Year and Month. I wish to add measure which will display the

1. Best Selling Month of Year

2. Sale value of the Best Selling Month.

While doing this exercise with MAXX and TOPN ,

the Grand Total displays correctly but values at year-month level displays incorrectly ( Highlighted in Orange ),

Expected 

• 908941.83 in all rows of [Sales of Best Selling Month of the Year (MAXX-TOPN)]  for 2017  and 
• December 2017 in all rows of [Best Selling Month of the Year (MAXX-TOPN)] for 2017

Measure that I used is 

Sales of Best Selling Month of the Year (MAXX-TOPN) =

MAXX(

     TOPN( 1, ALLSELECTED('Date'[Year Month]),[Sales Amount],DESC)

    ,[Sales Amount]

)

Best Selling Month of the Year (MAXX-TOPN) =

    MAXX(

        TOPN( 1, ALLSELECTED('Date'[Year Month]),[Sales Amount],DESC)

        ,[Year Month]

    )

Expected

Could anyone help in resolving this ?

[OwenAuger · Accepted solution]

Hi @SR_BI 

Below is how I would suggest writing these measures.

In both measures, I suggest using PARALLELPERIOD to modify the 'Date' filter to be the smallest set of complete year(s) containing the original 'Date' filter.

Sales of Best Selling Month of the Year =
CALCULATE (
    MAXX ( VALUES ( 'Date'[Year Month] ), [Sales Amount] ),
    PARALLELPERIOD ( 'Date'[Date], 0, YEAR )
)

Best Selling Month of the Year =
CALCULATE (
    FIRSTNONBLANK ( TOPN ( 1, VALUES ( 'Date'[Year Month] ), [Sales Amount], DESC ), 0 ),
    PARALLELPERIOD ( 'Date'[Date], 0, YEAR )
)

The 1st measure doesn't need TOPN since MAXX already returns the single maximum value regardless of ties.

In the 2nd measure, I used FIRSTNONBLANK as an arbitrary tie-breaker (following a pattern in this article) rather than MAXX, but either MINX/MAXX or FIRSTNONBLANK/LASTNONBLANK would be valid.

Do these work as expected for you?

Regards

[SR_BI]

Thank you very much.

Yes Parallelperiod solution it works as expected . Learnt more about FIRSTNONBLANK usage 

Sales of BSM of Year (Cal-MAXX-PP) =

CALCULATE(

    MAXX( VALUES('Date'[Year Month]), [Sales Amount] ),

    PARALLELPERIOD('Date'[Date] , 0 , YEAR)

)

BSM of Year C-PP =

CALCULATE(

    FIRSTNONBLANK( TOPN(1,VALUES('Date'[Year Month]),[Sales Amount] , DESC), 0),

    PARALLELPERIOD( 'Date'[Date] , 0 , YEAR)

)

Also realized my mistake , Matrix Row filter was on Date[Year] and Date[Month] and Date[Month] was sorted by Date[Month Number].

Without Parallelperiod Time intelligence function , rewrote using VALUES

Fixed Sales of BSM of Year (C-MAXX-VALUES) =

CALCULATE(

    MAXX

    (

        ALLSELECTED( 'Date'[Year],'Date'[Month], 'Date'[Month Number] ),

        [Sales Amount]

    )

    , VALUES('Date'[Year])  -- Include Year Context @ Month/Year/GT Level

)

Used Rand to break ties in Topn

Fixed BSM of the Year (TOPN) =

VAR TopSalesBreakTies =

CALCULATETABLE(

        TOPN(1,

            ALLSELECTED('Date'[Year Month]),

            [Sales Amount],DESC, RAND(),DESC

        ),

        REMOVEFILTERS( 'Date'[Month], 'Date'[Month Number])

)

RETURN

    MINX(

        TopSalesBreakTies,

        [Year Month]

    )

[SR_BI]

My Date table looks like