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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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