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.
Hi, I have a table like these:
Nacionality | Name | Nº childs |
Italian | Antonella | 12 |
Spanish | Hugo | 15 |
German | Franz | 13 |
Russian | Sacha | 11 |
Italian | Bianca | 17 |
Spanish | Lucas | 32 |
German | Kurt | 21 |
Russian | Vladimir | 27 |
Italian | Chiara | 49 |
Spanish | Martin | 3 |
German | Günter | 9 |
Russian | Dimitri | 14 |
Italian | Fiorella | 31 |
Spanish | Daniel | 37 |
German | Hans | 22 |
Russian | Igor | 23 |
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.
Solved! Go to Solution.
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])
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])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |