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
arquironsalud
Regular Visitor

SUM of TOP N by two categories

Hi, I have a table like these:

 

NacionalityNameNº childs
ItalianAntonella12
SpanishHugo15
GermanFranz13
RussianSacha11
ItalianBianca17
SpanishLucas32
GermanKurt 21
RussianVladimir27
ItalianChiara49
SpanishMartin3
GermanGünter9
RussianDimitri14
ItalianFiorella31
SpanishDaniel37
GermanHans22
RussianIgor23

 

I want to know the total sum of children with names that are in the Top 3 names of each nationality.
I explain this in more detail with the data in the table above:

 

Top 3 Italian names = Chiara (49), Fiorella (31) and Bianca (17)
Total number of children with Italian top 3 names = 97 (coming from 49+31+17)

 

Top 3 Spanish names = Daniel (37), Lucas (32) y Hugo (15)
Total children with Spanish top3 names = 84 (coming from 37+32+15)

 

Top 3 German names = Hans (22), Kurt (21) and Franz (13)
Total children with German top3 names = 56 (coming from 22+21+13)

 

Top 3 Russian names = Vladimir (27), Igor (23) and Dimitri (14)
Total children with Russian top3 names = 64 (coming from 27+23+14)

 

What I want to calculate with DAX is the total sum of children with names belonging to the Top 3 by nationality

Total children with names in Top3 of their nationality = 301 (comes from 97+84+56+64)


That 301 children is the measure that I need to obtain with the table that I indicated at the beginning

I hope you can help me. Thank you very much in advance.

 

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

Num with top names =
VAR SummaryTable = 
GENERATE(
	VALUES('Table'[Nacionality]),
	CALCULATETABLE(
		SELECTCOLUMNS(
			TOPN(3, 'Table', 'Table'[Nº childs]),
			"Name", 'Table'[Name],
			"Children", 'Table'[Nº childs]
		)
	)
)
return SUMX(SummaryTable, [Children])

View solution in original post

1 REPLY 1
johnt75
Super User
Super User

Num with top names =
VAR SummaryTable = 
GENERATE(
	VALUES('Table'[Nacionality]),
	CALCULATETABLE(
		SELECTCOLUMNS(
			TOPN(3, 'Table', 'Table'[Nº childs]),
			"Name", 'Table'[Name],
			"Children", 'Table'[Nº childs]
		)
	)
)
return SUMX(SummaryTable, [Children])

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.