Calculated column that excludes topN value from a column within the same table

[10168]

Dax Experts, I have a data model with calender (picture attached), I have the report with 'Fiscal Month Key' on X-axis, however I'm trying to use a new calculated column as X-axis on my report but I need to exclude TOPN value from the column, here (202208- FiscalMonthKey), I tried MAXX function, however I havent been successful, any help or direction is really appreciated. 

Eventually I'm trying to switch between these two axises (one that includes MAX month and one without). 

Thanks so much in advance 

[Anonymous · Accepted solution]

Hi @10168 ,

You can create a measure like:

Measure = 
var _max = CALCULATE(MAX('Calendar'[Fiscal Month Key]),ALL('Calendar'))
var _result = IF(MAX('Calendar'[Fiscal Month Key])=_max,1)
return
_result

 If you need to keep the external filter, you can replace ALL with ALLSELECTED.

Then set the visual level filter and bookmark:

With the bookmark button, you can freely switch back and forth.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[amitchandak]

@10168 , A new flag that can help

if([Fiscal Month key] =max([Fiscal Month key]), "Exclude", "Include")

or a meausre 

var _max = maxx(allselected(Date), date[Fiscal Month key])

return

calculate(sum(Table[value), filter(date, date[Fiscal Month key] <_max))