I don't know how to submit a pbix-file on this platform.
Here is the full code:
The result is a table with TopN grouped by "Land", ordered by "ID", sum of TopN, "others" and total sum.
Ergebnis =
VAR Auswahl =
IF(
HASONEVALUE(
AuswahlTopN[TopNumber]),
SELECTEDVALUE(AuswahlTopN[TopNumber]),
3)
VAR FullTable =
SUMMARIZE(
DeineTabelle,
DeineTabelle[Land],
"ID",
DISTINCTCOUNT(
DeineTabelle[ID]
)
)
VAR TopTable =
SUMMARIZE(
TOPN(
Auswahl,
SUMMARIZE(
DeineTabelle,
DeineTabelle[Land],
"ID",
DISTINCTCOUNT(
DeineTabelle[ID]
)
),
[ID],
DESC
),
DeineTabelle[Land],
"ID",
DISTINCTCOUNT(
DeineTabelle[ID]
)
)
VAR toptableranked = ADDCOLUMNS(TopTable, "Pos", RANKX(TopTable, [ID]))
VAR RestTable =
EXCEPT(
FullTable,
TopTable
)
VAR ToppoDistinctCount =
SUMX(
TopTable,
[ID]
)
VAR ToppoRow =
ADDCOLUMNS(
ROW("Land", "TopN", "IDValue", ToppoDistinctCount,"Pos", Auswahl+1),
"Source", "TopN"
)
VAR RestDistinctCount =
SUMX(
RestTable,
[ID]
)
VAR RestRow =
ADDCOLUMNS(
ROW("Land", "Rest", "IDValue", RestDistinctCount,"Pos", Auswahl+2),
"Source", "Rest"
)
VAR GesamtDistinctCount =
SUMX(
FullTable,
[ID]
)
VAR GesamtRow =
ADDCOLUMNS(
ROW("Land", "Gesamt", "IDValue", GesamtDistinctCount,"Pos", Auswahl+3),
"Source", "Gesamt"
)
VAR CombinedTable =
UNION(
ADDCOLUMNS(TopTableranked, "Source", "TopTable"),
ToppoRow,
RestRow,
GesamtRow
)
RETURN CombinedTable
