Dynamic TopN with 2 Layers knowing what's in "Others"

[primolee]

Hello everyone,

https://www.youtube.com/watch?v=yGFcCbXn_g0 

I use this tutorial from Goodly as reference to build my TopN model, however, I want to be able to find out what products are in "Others" so I can use matrix grid or bar chart with 2 layers, having "Others" on the first layer and then still be able to expand "Others" to find out what products are in "Others".

I tried many ways such as summarizing each product's sales in the table, but I then have no "Others" as product.  Could anyone please help me with this?  Thank you.

Best regards,

David

[CNENFRNL]

Obviously, the essence of the viz under the hood is just a flat table; it doesn't make any sense to drill down.

[primolee]

After sorting top N of your choice dynamically, if anyone wants to know what are in "Others", it will be necessary to drill down.  There might be hundreds of products, and I want to know what Others and their sales are.

I can have another similar table with all products in the report, but it will be redundant since I have the above table already.

[danextian]

Hi @primolee ,

Based on just the info provided, I'd create another measure for Others. So that would be:

Others = 
[Measure used in TopN] - [TopN Measure]

 Then use both TopN and Others in a visual.

[primolee]

Hello @danextian ,

Sorry I did not explain clear enough. (Or maybe I don't understand what you mean...)

for example:

Product	Sales	Rank
A	100	1
B	50	2
C	25	3
D	12	4
E	6	5
F	3	5
G	2	5

If I want Top 4, EFG will all be categoried as "Others" like the following pic.

But in this chart, I won't know what are in "Others".  I want to be able to expand the next level to show "Others" detail product.

Is there a way to do so?  Thanks.

Attached is the sample pbix provided by Goodly.

https://www.youtube.com/redirect?event=comments&redir_token=QUFFLUhqbU1yaldTX1dpMTh5MFhDdTJqZEU4eXlX... 

Best regards,

David

[danextian]

Hi @primolee,

So I created this measure and some others in DAX that might be handy for your use case.

Top Product Sales = 
VAR TopProducts =
    CALCULATE (
        [Sales],
        'Table'[Product] IN VALUES ( Products[Product] ),
        FILTER ( 'Table', 'Table'[Rank] <= 4 )
    )
VAR OtherProducts = CALCULATE (
        [Sales],
        FILTER ( 'Table', 'Table'[Rank] > 4 )
    )
RETURN
    IF (
        SELECTEDVALUE ( Products[Product] ) = "Others",
        OtherProducts,
        TopProducts
    )

Please refer to this PBIX for the details- https://drive.google.com/file/d/1PQNhrn2wbRIRcQrqDX2augi_N-4J5sZT/view?usp=sharing 

[primolee]

Hello @danextian ,

So close!  I just tried to modify your code, but still failed.  But I think we are getting very close.

The example in Goodly's video is having a dynamic Top N selection.  Therefore, the column "Rank" does not exist.  I can dynamically change how many top N I want.  I tried to replace "4" in your code with a rank measure and it does not work.  I think my ranking measure won't work in filter.

Please download the youtube link in my previous post, it is a pbix file.

https://chandeepchhabra-my.sharepoint.com/personal/chandeepchhabra_chandeepchhabra_onmicrosoft_com/_... 

Please use this pbix and see if you can solve it.

Thank you so much for your time and help!

Best regards,

David