Forum Discussion
Create a new table that categorizes values in a column based on columns and rows in another table
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 |
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])
)
2 Replies
- nilendraFabric
Super User
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])
)Thanks very much for the quick reply niledra. That did it!
