Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
weasel_girl
New Member

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: 

 

ProductShop typeShop category
Bread rollsSupermarketBig store
Bread rollsBakeryLittle store
Bread rollsFreezer shopLittle store
VodkaSupermarketBig store
ParacetamolSupermarketBig store
ParacetamolPharmacyLittle store
TomatoesFarmerLittle store

 

Shop category is a calculated column based on Shop type. 

 

I'd want the summarized output to be:

ProductMain category
Bread rollsBig store
VodkaBig store
ParacetamolBig store
TomatoesLittle 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 1
vicky_
Super User
Super User

Depending on why you need to summarize, you might not need to use a calculation for it: 

vicky__0-1689032785343.png

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.

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.