Forum Discussion
Summarize but only first value
I'm trying to summarize some data, but only want to keep the first instance of one of the categories.
Data example 'Shops' here:
| Product | Shop type | Shop category |
| Bread rolls | Supermarket | Big store |
| Bread rolls | Bakery | Little store |
| Bread rolls | Freezer shop | Little store |
| Vodka | Supermarket | Big store |
| Paracetamol | Supermarket | Big store |
| Paracetamol | Pharmacy | Little store |
| Tomatoes | Farmer | Little store |
Shop category is a calculated column based on Shop type.
I'd want the summarized output to be:
| Product | Main category |
| Bread rolls | Big store |
| Vodka | Big store |
| Paracetamol | Big store |
| Tomatoes | Little store |
Is there a way of doing this in SUMMARIZE?
At the moment my summarize command is:
ShopSummary = SUMMARIZE('Shops','Shops'[Product],'Shops'[Main Category])
but (understandably) I am getting error messages about duplicate values.
Any advice greatly appreciated.
1 Reply
Depending on why you need to summarize, you might not need to use a calculation for it:
If you do need to create a summary, you can consider using something like
Summarized = SUMMARIZE('Table', 'Table'[Product], "First Category", CALCULATE(MIN('Table'[Shop category]))Just a note: the MIN takes the first product alphabetically. If you have some index in your raw data, it's best that you use that in the calculation.
