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 which is a count the number of customer requests per day, showing top 10 customers and the rest group in "other"
I used the following Dax code given from another guide.
Catergory =
VAR Summary = SUMMARIZE('Appended Data','Appended Data'[Supplier],"Count", COUNT('Appended Data'[Supplier]))
VAR _Rank =
MAXX(
FILTER(
ADDCOLUMNS(
Summary,
"Rank", RANKX(Summary,[Count],Max([Count]),DESC,DENSE)
),
'Appended Data'[Supplier] = EARLIER('Appended Data'[Supplier])
),
[Rank]
)
RETURN
IF(_Rank<=10,'Appended Data'[Supplier], "Other")
This works beautifully however, I cannot figure out to to sort my customer request descendingly with others at the bottom instead
At the moment when I sort descending:
Name | Count |
Others | 435 |
Cust 1 | 126 |
Cust 2 | 65 |
Cust 3 | 45 |
Cust 4 | 34 |
Cust 5 | 30 |
Cust 6 | 29 |
Cust 7 | 27 |
Cust 8 | 23 |
Cust 9 | 19 |
Cust 10 | 11 |
but I want this:
Name | Count |
Cust 1 | 126 |
Cust 2 | 65 |
Cust 3 | 45 |
Cust 4 | 34 |
Cust 5 | 30 |
Cust 6 | 29 |
Cust 7 | 27 |
Cust 8 | 23 |
Cust 9 | 19 |
Cust 10 | 11 |
Others | 435 |
Appologies if this has been asked before, please link the solution.
Hi @Anonymous ,
You could try to create an index column like below
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc4xDsAgCIXhqzTMDiKIde4BegDjaNK52vsXJhy/P4+E1uBez3gnBGDK0EOD65vrQA2YxEPSINuA7GIzq4nd2Rzdok7VXczFfZrJXe2BbY/RAkLvPw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Count = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Count", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([Name]),each if Value.Is(Value.FromText(_), type number) then _ else null)))),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type1",null,10000,Replacer.ReplaceValue,{"Custom"})
in
#"Replaced Value"
Then set name sort by custom column in model, and set sort by name in table, you will get below result
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi my top 10 customer table is a custom column - when I used your code and customised it to may data it only ranks 10000 and 9997 - with other rows being errors. I will try to keep testing
Hi @Anonymous ,
You also could refer to below M code. If possible, could you please inform me your detailed information (such as your sample data(
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc67DcAgDATQVSLXFDHfUAJjIKZIsn98QpHd+Z2s081J472fg5kc8UnL/QHsswZegpzUQRyNozhEdYJNYRb7qi5wUV9wUFcMMP8yTgI2ARbxXtAa7r22d9zSvD4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Count = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Count", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([Name]),each if Value.Is(Value.FromText(_), type number) then _ else null)))),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom.1", each if [Custom]<11 and [Custom] <> null then [Name] else "others"),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Name", "Custom"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Custom.1"}, {{"sum", each List.Sum([Count]), type number}}),
#"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"Custom.1", "Name"}}),
#"Added Custom2" = Table.AddColumn(#"Renamed Columns", "Custom", each Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([Name]),each if Value.Is(Value.FromText(_), type number) then _ else null)))),
#"Replaced Value" = Table.ReplaceValue(#"Added Custom2","","100000",Replacer.ReplaceValue,{"Custom"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value",{{"Custom", Int64.Type}})
in
#"Changed Type2"
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I have to change real data into stock data in the file as its sensitive my company but otherwise the columns are all there. Hope this will not affect you trying to help me
The branch and category is there for me to use in the matrix so that it shows up with 0 instead of blanks.
They are created using the New table using Dax expression - because the category of suppliers will dynamically change.
If you need more information please let me know.
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 |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |