Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Top N, others and null values

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...
Need Help
v-kelly-msft's avatar
v-kelly-msft
Community Support
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    v-kelly-msft
    Community Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        Can anyone help me with this problem?