cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Max Monthly Sales using MAXX TOPN Shows incorrect total

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 ?

1 ACCEPTED SOLUTION
Super User

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

Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
3 REPLIES 3
Super User

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

Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Frequent Visitor

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]

)

Frequent Visitor

My Date table looks like

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors