Relationships: Group not defined values into "Others"

[paliman]

Hello all

I am working in a Budget vs Actual scenario.

In the "Budget table" I have a detailed budget for some major customers, and a lot of small customers grouped as "Other".

In the "Actuals table" I have all these small customers one by one

I also have two dimension tables named "Customers" with all master data and "Budget Customers", with only our major customers and the "other" item

These are the dimension tables:

Both are related by "Cust ID" and "Budget Cust ID"

The Fact Tables:

And the desired result:

I can´t group the actual sales to small customers into the other group

Thanks in advance

Pablo.

[edhans · Accepted solution]

I would do this in Power Query before bringing in the data to your DAX model. THat is what Power Query is designed for.

It turns this:

into this:

Put this M code in a blank query to see what I did. I didn't have real data or know what your decision point was on what was a real customer and what got summarized into "Other Customers" so I arbitrarly chose to look at the first 5 characters of the fake customer names I was using. You could do it off of sales value, or some other field that you may not have shown, like customer class, category, etc.

Note too before bringing it in to to DAX, you could even do a group by on the first 2 columsn and summarize sales and only bring in one "Other Customer" line with a total of their sales on one line if you wish.

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIuLS7Jz00tArENDQwMlGJ1opWMgBz/kozUouTSYpCEiSlY2BhJGKTEEqg6FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Column1", type text}, {"Column2", Int64.Type}}),
    #"Added NewID" = Table.AddColumn(#"Changed Type", "NewID", each if Text.Start([Column1], 5) = "Other" then 999 else [ID], Int64.Type),
    #"Added New Name" = Table.AddColumn(#"Added NewID", "New Name", each if [NewID] = 999 then "Other Customers" else [Column1], type text),
    #"Removed Other Columns" = Table.SelectColumns(#"Added New Name",{"NewID", "New Name", "Column2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"NewID", "ID"}, {"New Name", "Name"}, {"Column2", "Sales"}})
in
    #"Renamed Columns"

[paliman]

Thanks to both of you. The approach of using Power Query was right.

[edhans]

Glad your project is moving forward now @paliman.

[amitchandak · Accepted solution]

@paliman, I would prefer to one customer dimension table the one like 2nd one and append a new row in that

Other, Other customers

Or

-1, Other Customer

This can be done either having a new table with that row and merge them in Edit query mode.

[edhans · Accepted solution]

I would do this in Power Query before bringing in the data to your DAX model. THat is what Power Query is designed for.

It turns this:

into this:

Put this M code in a blank query to see what I did. I didn't have real data or know what your decision point was on what was a real customer and what got summarized into "Other Customers" so I arbitrarly chose to look at the first 5 characters of the fake customer names I was using. You could do it off of sales value, or some other field that you may not have shown, like customer class, category, etc.

Note too before bringing it in to to DAX, you could even do a group by on the first 2 columsn and summarize sales and only bring in one "Other Customer" line with a total of their sales on one line if you wish.

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIuLS7Jz00tArENDQwMlGJ1opWMgBz/kozUouTSYpCEiSlY2BhJGKTEEqg6FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Column1", type text}, {"Column2", Int64.Type}}),
    #"Added NewID" = Table.AddColumn(#"Changed Type", "NewID", each if Text.Start([Column1], 5) = "Other" then 999 else [ID], Int64.Type),
    #"Added New Name" = Table.AddColumn(#"Added NewID", "New Name", each if [NewID] = 999 then "Other Customers" else [Column1], type text),
    #"Removed Other Columns" = Table.SelectColumns(#"Added New Name",{"NewID", "New Name", "Column2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"NewID", "ID"}, {"New Name", "Name"}, {"Column2", "Sales"}})
in
    #"Renamed Columns"