Bar chart - TopN with others and with legend

[Anonymous]

Hi all, 

I created a top N for a bar chart, but I wanted to see a column with the others? it's possible?
And the method I'm using works, but if I put a field on legend it no longer does the TopN, any suggestions?

I created a parameter (this is my topN slicer) and after i created this measure: 

TOP N = 
VAR SelectedTop = SELECTEDVALUE('TopN'[TopN])
RETURN
SWITCH(TRUE(),
    SelectedTop = 0, 'Teste'[Sum Qty],
    RANKX ( 
            ALLSELECTED(  'Teste'[Product] ), 
            'Teste'[Sum Qty]
                )
                  <= SelectedTop,
        'Teste'[Sum Qty]
)

This is the test file: https://1drv.ms/u/s!AkZ8wrQeLhnChQLnr0qzvPlk39sR

Kind regards. 

[wma2323]

I can't get this to work at all.  Only when I slide the slicer to 1 does it show the top result and other, but from 2-10 it just shows "Other"

[MFelix]

Hi @wma2323

Can you share the metric you are using please. 

[wma2323]

Quick question before I do that.  If there is a blank in the product list with other, will that throw off the calc?

[MFelix]

Yes it can do it. 

Blank values always can add issues to your calculation 

[wma2323]

[MFelix]

Hi @wma2323 ,

You issue is with the SELECTEDVALUE(TopN) part and the way the slicer is used.

SELECTEDVALUE only returns a single value from the data, so in case you have more than one value on your selection (has you have in the second image) the result is blank, so you get only a row for others.

In this case you need to change your slicer to be a single select  one or change the formula to a MAX instead of SelectedVAlue

VAR SelectedTop =
   MAX('TopN'[TopN])

If you replace the variable you will get expected result.

[wma2323]

That did it!  Thanks!

[MFelix · Accepted solution]

Hi @Anonymous,

Using this post by @TomMartens, you can get the solution.

I have made some adjustments to get the TOPN based on your selection but the rest is from the post.

• Create an unrelated table with the following code:

Products_and_Others =
UNION (
    SUMMARIZE ( ALL ( Teste[Product] ); Teste[Product] );
    ROW ( "Product"; "Other" )
)

• Create the following measure:

top 5 and other =
VAR SelectedTop =
    SELECTEDVALUE ( 'TopN'[TopN] )
VAR top5 =
    CALCULATETABLE (
        TOPN (
            SelectedTop;
            VALUES ( Teste[Product] );
            CALCULATE ( SUM ( Teste[Qty] ) )
        )
    )
VAR other =
    ROW ( "Product"; "Other" )
VAR allTheRest =
    CALCULATE ( SUM ( Teste[Qty] ); EXCEPT ( VALUES ( Teste[Product] ); top5 ) )
VAR theUnion =
    UNION ( top5; other )
RETURN
    SUMX (
        INTERSECT ( 'Products_and_Others'; theUnion );
        VAR currentIterator = 'Products_and_Others'[Product]
        RETURN
            IF (
                'Products_and_Others'[Product] <> "Other";
                CALCULATE ( SUM ( Teste[Qty] ); Teste[Product] = currentIterator );
                allTheRest
            )
    )

Then use the unrelated table on the x-axis and this measure on the data values.

PBIX file attach, I have highlited the Others on the Data Colors.

Regards,

MFelix

[Anonymous]

@MFelix  Thank you very much !!