Summarized table to show top 10 of each category

[KellerB]

Hello,

I am trying to create a summarized table off raw data. This summarized table will show me the top "X" selling products (based on revenue) in each product family, for each region... I would also like to see the percent of the grand total each product makes for the product family and region. So logically, I need to find the sum of total revenue for each product family and Product Number in each Region. Then I need to compare the sum of the product number to the overall product family to determine the highest sold product.

Below is a sample data table of the columns and data I am working with. Let me know if you are able to help! thanks!!

gbu	GBU cust	lob	region	product_family	Product Number	Quantity	Sum of monthly_revenue_usd_amount
IRS	IRS	1ph	AMERICAS	A	A1	3	25
IRS	IRS	1ph	AMERICAS	A	A1	2	30
IRS	IRS	1ph	AMERICAS	A	A2	5	50
IRS	IRS	1ph	AMERICAS	A	A2	6	75
IRS	IRS	1ph	AMERICAS	B	B1	10	60
IRS	IRS	1ph	AMERICAS	B	B1	9	40
IRS	IRS	1ph	AMERICAS	B	B2	8	90
IRS	IRS	1ph	AMERICAS	B	B2	4	15
IRS	IRS	Rack	AMERICAS	C	C1	6	20
IRS	IRS	Rack	AMERICAS	C	C1	2	40
IRS	IRS	Rack	AMERICAS	C	C2	3	250
IRS	IRS	Rack	AMERICAS	C	C2	1	160
IRS	IRS	Rack	AMERICAS	D	D1	2	20
IRS	IRS	Rack	AMERICAS	D	D1	7	400
IRS	IRS	Rack	AMERICAS	D	D2	8	10
IRS	IRS	Rack	AMERICAS	D	D2	4	250
IRS	IRS	Rack	AMERICAS	D	D2	6	70

Expected output for the top 2 products for each family in each region:

Region	Product Category	Product Family	Product Num	Qty	Rev	% of LoB
Americas	1Ph	A	A2	11	125	32%
Americas	1Ph	B	B2	12	105	27%
Americas	Rack	C	C2	4	410	34%
Americas	Rack	D	D1	9	420	34%

I will have multiple regions, product categories and product families in the real data.

[KellerB]

Hello Ryan,

Thanks for the note! I am trying to get the ranking for each lob for each Region. This seems to look at the lob globally.

So my real data will have Americas, Europe, and Asia. So i would like the top "X" revenue products for each lob for each region. Let me know if this makes sense or if I need to elaborate.

[ryan_mayu · Accepted solution]

maybe try this

Table 2 = 
VAR tbl=ADDCOLUMNS( SUMMARIZE('Table','Table'[region],'Table'[lob],'Table'[product_family],'Table'[Product Number],"qty",sum('Table'[Quantity]),"rev",sum('Table'[Sum of monthly_revenue_usd_amount])),"%lob",[rev]/ CALCULATE(sum('Table'[Sum of monthly_revenue_usd_amount]),ALLEXCEPT('Table','Table'[lob] )))
var tbl2=ADDCOLUMNS(tbl,"rank",rankx(filter(tbl,[lob]=EARLIER([lob]) && [region]=earlier[region]),[rev],,DESC))
return filter(tbl2,[rank]<3)

[ryan_mayu]

@KellerB 

you can try this to create a new table

Table 2 = 
VAR tbl=ADDCOLUMNS( SUMMARIZE('Table','Table'[region],'Table'[lob],'Table'[product_family],'Table'[Product Number],"qty",sum('Table'[Quantity]),"rev",sum('Table'[Sum of monthly_revenue_usd_amount])),"%lob",[rev]/ CALCULATE(sum('Table'[Sum of monthly_revenue_usd_amount]),ALLEXCEPT('Table','Table'[lob] )))
var tbl2=ADDCOLUMNS(tbl,"rank",rankx(filter(tbl,[lob]=EARLIER([lob])),[rev],,DESC))
return filter(tbl2,[rank]<3)