cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bfra4 Member
Member

Bar chart - TopN with others and with legend

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Bar chart - TopN with others and with legend

Hi @bfra4,

 

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



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

Proud to be a Datanaut!




2 REPLIES 2
Super User
Super User

Re: Bar chart - TopN with others and with legend

Hi @bfra4,

 

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



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

Proud to be a Datanaut!




bfra4 Member
Member

Re: Bar chart - TopN with others and with legend

@MFelix  Thank you very much !! Smiley Happy