topic Correcting the blank value returned in TOPN for negative numbers in DAX Commands and Tips

Correcting the blank value returned in TOPN for negative numbers

Anonymous — Mon, 09 May 2022 13:02:37 GMT

Hi, everyone. I'm trying to create a card which shows the name of the manager whose store ranked with most financial losses per month. I've implemented this with a data slicer and a map. The data slicer is segmented by State and Store Name and I wanna see the name of the manager of each store if it is selected in the data slicer too. However, there's a limitation in the Dax Formula I'm using it. That's because the value of financial losses can be negative or positive and whenever I select a store with negative value on the data slicer, my card returns blank value.

I'm using the following measure to show the Top1 manager of the month:

Coordenador = CALCULATE(FIRSTNONBLANK('ABRIL 22'[COOR ATU],1),TOPN(1,ALLSELECTED('ABRIL 22'[COOR ATU]),[Perda Total],DESC))

Could somebody give me a hint?

Re: Correcting the blank value returned in TOPN for negative numbers

Whitewater100 — Mon, 09 May 2022 13:29:50 GMT

Hi:

Do you have a small example file to help understand? You can use ABS[Measure Name]) to give absolute values so it won't matter if positive or negative(if that's what you want).

Generally, this measure should help get top 1 no matter the tme frame you slice on:

If [Perda Total] = the financial loses and you have a separate dim

table for all of your unique financial managers names, e.g. Manager[Mgr Name] as one column in this table.

Top1 mgr = CALCULATE([Perda Total],
TOPN(1, Managers[Mgr Name], DESC)

You should also have a separate Date Table where you can slice on month, year ,etc and the Top1 will show up based on your slections.

I hope this helps!

P.S. An example model is placed below so you can see the relationships which brings all the nalytical power.

Re: Correcting the blank value returned in TOPN for negative numbers

Anonymous — Mon, 09 May 2022 13:52:05 GMT

Currently, the dashboard is like this. In fact, there are 3 types of job titles related for each store. All three are blank whenever I select a Store with negative value for Total Loss. I don't think ABS() function would solve my problem because a negative value could rank higher than a positive value using this function. I just would like a way to use TOPN or another function considering negative and positive numbers on the ranking.

Re: Correcting the blank value returned in TOPN for negative numbers

v-easonf-msft — Thu, 12 May 2022 03:14:27 GMT

Hi, Anonymous

Top N does not work with negative numbers.

Please try to replace with function 'SAMPLE' .

Coordenador2 = CALCULATE(FIRSTNONBLANK('ABRIL 22'[COOR ATU],1),SAMPLE(1,'ABRIL 22',[Perda Total],DESC))

You can also create a sort column 'Rank' using ‘RANKX’, then filter data with ‘Rank’=1.

Please refer this related tread.

topn-dax-for-negative-values

Best Regards,
Community Support Team _ Eason