Dynamic topn + Other category and subcategory

[superBro22]

I have a table of products with category and subcategory and I would like to get a Matrix Report with the TopN + other of category and subcategory.

I'm trying to rank my table dynamically both in category and subcategory by sales  but I can't find a way to do it.

This is the code to get the table:

Table =

    DATATABLE

        (

        "Category"      , STRING ,

        "Sub Category"  , STRING ,

        "Date"          , DATETIME ,

        "Sales"      , INTEGER ,

        "Profit"      , INTEGER ,

            {

            {"A","A1","2018-01-01", 2,2},

            {"A","A2","2018-01-02", 4,2},

            {"A","A3","2018-01-03", 6,6},

            {"A","A4","2018-01-04", 6,6},

            {"B","B1","2018-01-05",21,2},

            {"B","B2","2018-01-06",22,2},

            {"B","B2","2018-01-07",23,2},

            {"C","C1","2018-01-08",35,3},

            {"C","C2","2018-01-09",35,3},

            {"C","C3","2018-01-10",35,3},

            {"","","2018-01-11",35,3},

            {"C","","2018-01-12",36,3},

            {"","C1","2018-01-13",36,3}

            }

        )

Can someone help me with this please?

[superBro22]

I have also tried to create the following measure :

Visual Control =

IF(HASONEVALUE('Table'[Category])&&HASONEVALUE('Table'[Category])&& [Sales] >0,

var _categorytopn =

SELECTCOLUMNS(

    TOPN(

        SELECTEDVALUE('TopN'[TopN]),

        SUMMARIZE(

        ALL('Table'),

        'Table'[Category],

        "Sales",SUM('Table'[Sales])

        ),

        [Sales]

    ),

    "Category",

    [Category]

)

var _subcategorytopn =

SELECTCOLUMNS(

    TOPN(

        SELECTEDVALUE('TopN'[TopN]),

        SUMMARIZE(

            FILTER(

               ALL('Table'),

               'Table'[Category]=SELECTEDVALUE('Table'[Category])

            ),

            'Table'[Category],

            'Table'[Sub Category],

            "Sales",SUM('Table'[Sales])

        ),

        [Sales]

    ),

    "Level2",

    [Category]&"-"&[Sub Category]

)

return

IF(

    ISINSCOPE('Table'[Category])&&NOT(ISINSCOPE('Table'[Sub Category])),

    IF(

        SELECTEDVALUE('Table'[Category]) in _categorytopn,

        1,0

    ),

    IF(

        ISINSCOPE('Table'[Sub Category]),

        IF(      

            SELECTEDVALUE('Table'[Category]) in _categorytopn&&

            SELECTEDVALUE('Table'[Level2]) in _subcategorytopn,

            1,0

        )

        )

    )

But the result is still not as expected.

[v-xiaotang]

Hi  @superBro22 

Thanks for reaching out to us.

please give your expected output, when category and sub category are empty, what the expected result should be?

Best Regards,

Community Support Team _Tang

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

[superBro22]

Hi @v-xiaotang  Thanks in advance for your help.

I would like the TopN to be dynamic and not include blank categories and subcategories and for duplicate sales values to be sorted correctly.

I have tried to create the following measure :

Rank Product = IF ( ISINSCOPE('Table'[SubCategory] ), RANKX ( CALCULATETABLE ( VALUES ( 'Table'[SubCategory] ), ALLSELECTED ('Table'[Sub Category] ) ), [You go out] ) )

But the result is not what expected:

This is my expected output for TopN = 3:

This is the code to get the table:

Table =

    DATATABLE

        (

        "Category"      , STRING ,

        "Sub Category"  , STRING ,

        "Date"          , DATETIME ,

        "Sales"      , INTEGER ,

        "Profit"      , INTEGER ,

            {

            {"A","A1","2018-01-01", 2,2},

Do you think there is a way to get to this result? Thank you!

            {"A","A2","2018-01-02", 4,2},

            {"A","A3","2018-01-03", 6,6},

            {"A","A4","2018-01-04", 6,6},

            {"B","B1","2018-01-05",21,2},

            {"B","B2","2018-01-06",22,2},

            {"B","B2","2018-01-07",23,2},

            {"B","B3","2018-01-07",20,2},

            {"C","C1","2018-01-08",35,3},

            {"C","C2","2018-01-09",35,3},

            {"C","C3","2018-01-10",35,3},

            {"","","2018-01-11",35,3},

            {"C","","2018-01-12",36,3},

            {"","C1","2018-01-13",36,3}

            }

        )

Do you think there is a way to get to this result? Thank you!