Forum Discussion

buttercream's avatar
buttercream
Helper II
1 year ago
Solved

RankX help

I'm trying to find the product with the highest number of rows for each date.  What would be the best way to change:

 

This table:

MonthProduct
Sep'24A
Sep'24B
Sep'24A
Sep'24A
Sep'24A
Aug'24B
Aug'24B
Aug'24B
Aug'24A

 

To this:

MonthTop ProductCount
Sep'24A4
Aug'24B3
Need Help

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    1 year ago

    buttercream Well, you could put Month and Product in a table visual and then simply:

    Measure = COUNTROWS( 'Table' )

     

    Or you could create a table like:

    Table1 = SUMMARIZE( 'Table', [Month], [Product], "Count", COUNTROWS( 'Table' ) )

    • buttercream's avatar
      buttercream
      Helper II
      1 year ago

      That gives me the count for each product for each month.  How do I get the top product in each month only?  Sep'24 should only show A with 4.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi, buttercream 

    You can also try the following DAX expressions to create a new table

     

    TopProductTable = 
VAR _vtable = SUMMARIZE(
    'Table',
    'Table'[Month],
    'Table'[Product],
    "_Count", COUNT('Table'[Product])
)
RETURN 
SELECTCOLUMNS(FILTER(ADDCOLUMNS(_vtable,"_A",MAXX(FILTER(_vtable,'Table'[Product]=EARLIER('Table'[Product])),[_Count])),[_Count]=[_A]),'Table'[Month],'Table'[Product],[_Count]
)

     

     

    Here is my preview:

     

    How to Get Your Question Answered Quickly 

    Best Regards

    Yongkang Hua

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