Top N, others and null values

[Anonymous]

Hi,

I am no PowerBI expert. I have read multiple topics on this issue and have been trying things out for hours, but I still haven't reach the desired outcome.

I have created a test file, see the link below. I would like to have a top 3 "Product" based on the "Sales" numbers. The rest of the products must be combined in an "Others" category.

Now here is the catch. When you open the data you will see that the "Product" with the most amount of "Sales" has a null value. By default I would like to add this null value in the "Others" category. So since the null value "Product" is excluded in the ranking the top 3 actually contains the top 2 t/m 4. When for example the null value is on position 3 in the top 3 (when only filtering the year 2018) the top 3 actually contains product 1, 2 and 4.

I hope my question is clear!

Top 3.pbix 

Thanks in advance!

Rotjer

[v-kelly-msft]

Hi  @Anonymous ,

Create a measure as below:

Measure = 
var _sumtotal=SUMX(FILTER(ALL(Data),'Data'[Product]=MAX('Data'[Product])&&'Data'[Year]=MAX('Data'[Year])&&'Data'[Product]<>BLANK()),'Data'[Sales])
var _top=TOPN(3,FILTER('Data','Data'[Product]<>BLANK()),_sumtotal,DESC)
Return
SUMX(_top,'Data'[Sales])

Then put the measure in the filter pane ,choose top 3：

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hi @v-kelly-msft ,

Thanks for your respons! This indeed fixed the issue with the blank "Product". The only thing missing is the "Others" category including the blank "Product".

Best regards,

Rotjer

[v-kelly-msft]

Hi  @Anonymous ,

So you want the blank rows to be grouped as "others",then be ranked in order,right?

If so ,first create a new column as below:

Product new = IF('Data'[Product]=BLANK(),"others",'Data'[Product])

Then create a measure as below;

Measure = 
var _sumtotal=SUMX(FILTER(ALL(Data),'Data'[Product new]=MAX('Data'[Product new])&&'Data'[Year]=MAX('Data'[Year])),'Data'[Sales])
var _top=TOPN(3,'Data',_sumtotal,DESC)
Return
SUMX(_top,'Data'[Sales])

You will see:

For the updated .pbix file,pls see attached.

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hi @v-kelly-msft,

Thanks for your time!

I actually want a top 3 category with an extra "others" category. The "others" category contains the blank rows and the other rows which didn't make the top 3. So basically the totals will always be the same but the grouping will be different. See the result that I want below:

Current:

Product	Sales
	920
D	890
C	785
B	760
E	730
A	705
Totals	4790

New:

Product	Sales
D	890
C	785
B	760
others	2355
Totals	4790

Kind regards,

Rotjer

[Anonymous]

Can anyone help me with this problem?

[amitchandak]

@Anonymous , refer if this can help

https://community.powerbi.com/t5/Desktop/RANKX-excluding-blanks/m-p/397656#M181491

[Anonymous]

Hi @amitchandak,

I indeed already seen that post, but it wasn't helpful enough for me. Like I said I'm not a pro. The difference with my situation is that my "Category" (Club) is blank and not the values (YOY).

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format? 

[Anonymous]

@amitchandak Can you download Top3.pbix in my first post?