Create a new table that categorizes values in a column based on columns and rows in another table

[abrother]

Hi everyone, thank you in advance for the help, this community has been great with input and fast replies.

What I'm trying to do is create a new table that takes values from a few columns (and rows) from another table and creates a categorization against production orders (lots basically). The intent is to create a table that shows, for each production order, what the revenue is based on the quantity of serial numbers of that SKU in that particular revenue category where each SKU has an already assigned revenue category, but multiple SKUs come out of each production order; each revenue category has a baseline budget revenue that we expect from the whole production order which is currently listed on each row. Sample data is below, let me know if you have any questions:

Here's what I have:

Production Order	SKU	SKU ASP	Serial Number	Revenue Category	Budget Category Revenue per SKU
A	123	$75	111	CAT1	$1,000
A	123	$75	112	CAT1	$1,000
A	123	$75	113	CAT1	$1,000
A	123	$75	114	CAT1	$1,000
A	123	$75	115	CAT1	$1,000
A	456	$100	222	CAT2	$800
A	456	$100	223	CAT2	$800
A	456	$100	224	CAT2	$800
A	456	$100	225	CAT2	$800
A	456	$100	226	CAT2	$800
B	123	$75	111	CAT1	$1,000
B	123	$75	112	CAT1	$1,000
B	123	$75	113	CAT1	$1,000
B	123	$75	114	CAT1	$1,000
B	123	$75	115	CAT1	$1,000
B	789	$110	222	CAT3	$1,100
B	789	$110	223	CAT3	$1,100
B	789	$110	224	CAT3	$1,100
B	789	$110	225	CAT3	$1,100
B	789	$110	226	CAT3	$1,100

Here's what I want:

Production Order	Revenue Category	Budget Category Revenue	Total Actual Category Revenue
A	CAT1	$1,000	$375
A	CAT2	$800	$500
B	CAT1	$1,000	$375
B	CAT3	$1,100	$550

[nilendraFabric · Accepted solution]

Try this

SummaryTable =
SUMMARIZE(
ProductionOrders,
ProductionOrders[Production Order],
ProductionOrders[Revenue Category],
"Budget Category Revenue", MAX(ProductionOrders[Budget Category Revenue per SKU]),
"Total Actual Category Revenue", SUM(ProductionOrders[SKU ASP])
)

[abrother]

Thanks very much for the quick reply niledra. That did it!