RankX help

[buttercream]

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:

Month	Product
Sep'24	A
Sep'24	B
Sep'24	A
Sep'24	A
Sep'24	A
Aug'24	B
Aug'24	B
Aug'24	B
Aug'24	A

To this:

Month	Top Product	Count
Sep'24	A	4
Aug'24	B	3

[Anonymous]

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

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[buttercream]

Thanks.  This works.

[Ashish_Mathur]

You are welcome.

[Greg_Deckler]

@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]

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.