Top N Categories and products of each category

[Kcponakala]

Hi,

I need to show top N categories and their production in the Matrix with Catg--> Product heirarchy.

Please help me on this

Top 3 Categories
Category	Product	Sales	Rank
Catg1		100	3
	PROD1	80
	PROD2	20
Catg2		150	2
	PROD3	100
	PROD4	20
	PROD5	30
Catg3		300	1
	PROD6	100
	PROD7	200

Thanks,

Kalyan

[v-gizhi-msft · Accepted solution]

Hi,

Please try this measure:

Measure =
IF (
    ISINSCOPE ( 'Table'[Product] ),
    BLANK (),
    RANKX (
        SUMMARIZE (
            ALLSELECTED ( 'Table' ),
            'Table'[Category],
            "Total", SUM ( 'Table'[Sales] )
        ),
        [Total],
        SUM ( 'Table'[Sales] ),
        DESC,
        DENSE
    )
)

The result shows:

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto

[amitchandak]

@Kcponakala ,

Refer this should help

https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/

[edhans]

While certianly possible, this would be much MUCH easier for us to help to get a copy of  your source table data, not your matrix. Otherwise we have to recreate the data for you.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum