Distinct Top N by Slicer and Top N by Category

[qadir]

Hi, I want to present top N by Slicer, for example when I select 5, it shows 8 categories instead of Top 5. becuase the sales amount for these categories are same, how can I present only top 5 and when I select Company from other slicer it should present top 5 for respective company. Thank you.

Power BI file link:

https://drive.google.com/file/d/1Eoa3McbxUShIcL8NFpczhMKX7BZ_qoMe/view?usp=sharing

Top N by Slicer and Company.pbix

https://1drv.ms/u/s!AjITNjmTPlzbhgkh4g_6DjsHCNWW?e=OlShRe

Please help, Thank you.

[v-xuxinyi-msft · Accepted solution]

Hi @qadir 

I would like to apologize for the belated reply.

Here is my testing:

1. Do the following in Transform data as shown below

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MACSjkqxOtFKTkCWEZjvBOY7w/kQeRcgyxiJ74om7wZkmSDpd4ebD+F7AFmmSHxPIMsMSb8Xmnu80cz3QTYvFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Sales = _t, Company = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Sales", Int64.Type}, {"Company", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Sales", Order.Descending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Company"}, {{"index", each Table.AddIndexColumn(_, "Index.1", 1, 1, Int64.Type)}}),
    #"Expanded index" = Table.ExpandTableColumn(#"Grouped Rows", "index", {"Category", "Sales", "Index", "Index.1"}, {"index.Category", "index.Sales", "index.Index", "index.Index.1"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded index",{{"index.Sales", Int64.Type}, {"index.Index", Int64.Type}, {"index.Index.1", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"index.Category", "Category"}, {"index.Sales", "Sales"}, {"index.Index", "Index"}, {"index.Index.1", "Index.1"}})
in
    #"Renamed Columns"

2. Create a measure as follows

Top N Items = 
IF (
    SELECTEDVALUE ( 'Table 2'[Company] ) = BLANK ()
        && SELECTEDVALUE ( 'Table'[Index] ) <= Parameter[Parameter Value],
    1,
    IF (
        SELECTEDVALUE ( 'Table 2'[Company] ) <> BLANK ()
            && SELECTEDVALUE ( 'Table'[Company] ) = SELECTEDVALUE ( 'Table 2'[Company] )
            && SELECTEDVALUE ( 'Table'[Index.1] )
                <= SELECTEDVALUE ( Parameter[Parameter] ),
        1,
        0
    )
)

3. Put the measure into the filter so that the visual only shows data where the measure is equal to 1

Is this the result you expect?

Best Regards,
Community Support Team _Yuliax

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[qadir]

Thank you for reply but in my case, I'm not ranking on the column instead i want to present rank based on measure. First I calculate the measure 

Number of Documents = DISTINCTCOUNT(TableName[DocumentNum])

2nd Measure

POs = CALCULATE([Number of Documents],TableName[DocumentType]="PO")

3rd Measure

Top N Items =

CALCULATE (

    [POs],

    KEEPFILTERS (

        TOPN (

            'Top N'[Top N Value],

            ALLSELECTED (TableName[ItemName] ),

            [POs],

            DESC

        )

    )

)

 In this case, How can I present the distinct top N? You can see in the above screenshots, I tried to rank the items based on measure. How can i present only top 5 instead of  7?

[Kirubs]

Hi, 

I believe you need to use the Rank funtion on the Sales and if the sales amount is the same, you need to have another column to break the tie and in this case I assume it will the company name sorted in alphabetical order.   You can define a rank tie based on multiple column. for ex. Sales and then category and company name until you find a unique combination to rank the data. 

You will find more details on this post. 

https://community.fabric.microsoft.com/t5/Community-Blog/Breaking-Ties-in-Rankings-with-RANKX-Using-...

Regards,
Kiruba
If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Kirubs]

Hi, 

I believe you need to use the Rank funtion on the Sales and if the sales amount is the same, you need to have another column to break the tie and in this case I assume it will the company name sorted in alphabetical order.   You can define a rank tie based on multiple column. for ex. Sales and then category and company name until you find a unique combination to rank the data. 

You will find more details on this post. 

https://community.fabric.microsoft.com/t5/Community-Blog/Breaking-Ties-in-Rankings-with-RANKX-Using-...

Regards,
Kiruba
If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.