Dynamic Other category based on count

[PoojaG]

I have the following sample data:

Industry Department Project            Name

IT	Regional	Consulting	Fiona
IT	Regional	Consulting	Gary
IT	Regional	Consulting	Helen
IT	Regional	Customer Svc	Julia
IT	International	Development	Quinn
IT	International	Development	Rachel
IT	International	Development	Sam
IT	International	Engineering	Rachel
IT	International	Engineering	Sam
IT	International	Engineering	Tom
IT	Regional	Consulting	Lara
IT	Regional	Consulting	Mia
IT	Regional	Consulting	Rumi
IT	Regional	Customer Svc	Jake
IT	Regional	Customer Svc	Liam
IT	Regional	Customer Svc	Jordy
IT	International	Development	Taylor
IT	International	Development	Swift
IT	International	Development	Maya
IT	Global	Events	Laura
IT	Global	Events	Mike
IT	Global	Events	n Nancy
IT	Global	Financial	Kevin
IT	Global	Financial	Mark
IT	Global	Financial	Mark
IT	Global	Logistics	Oscar
IT	Global	Logistics	Patricia
IT	Global	Logistics	Quentin
IT	Global	Marketing	Alice
IT	Global	Marketing	Bob
IT	Global	Marketing	Charlie
IT	Global	Marketing	Daisy
IT	Global	Operations	Calvin
IT	Global	Operations	Diane
IT	Global	Operations	Edward
IT	Global	Product Dev	Ethan
IT	Global	Product Dev	Fiona
IT	Global	Product Dev	Gabriel
IT	Global	Production	Tim
IT	Global	Production	Ursula
IT	Global	Production	Victor
IT	Local	Research	Nathan
IT	Local	Research	Olivia
IT	Local	Intern	Le
IT	Local	Intern	Ben
IT	Local	Research	Peter
IT	Local	Sales	Wendy
IT	Local	Sales	Xavier
IT	Local	Sales	Za
IT	Local	Sales	Yvette
IT	Local	Sales	Zoe
IT	Local	Sales	Aaron
IT	Local	Sales	Bella
IT	Local	Training	Isaac
IT	Local	Training	Jane
IT	Local	Training	Kyle
IT	Local	Research	Nate
IT	Local	Research	Love
IT	Local	Research	Pete
IT	Local	Sales	Wen
IT	Local	Sales	Xavier
IT	Local	Sales	Tate
IT	Local	Sales	Zoo
IT	Local	Sales	Aaron

I need to summarize the data in a matrix format like below:

Count is a simple calculated measure distinctcount(Name). I would like to add a new Department Category "Other" under Industry where the Count <=10. Which should look like below:

So International and Regional got grouped as "Other". Additionally, it should go further deep if the Count under Project category <=10. So final output should look like below:

I tried the solution provided here but didn't get the solution I'm looking for. I have other calculated measures beyond the count but don't want to make it complicated than this at this point.

thank you for your help!

[adudani · Accepted solution]

hi @PoojaG ,

Approach via Power Query below:

create 2  blank queries, Copy and paste the below code into the advanced editor

source data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZXbcuIwDIZfheG6L1GgZWlDoZA9dnohHC1o69iM4qSTt187KbsJNXZaruzosyzpl8XT03ihsrIwXI/6v/HVeIZHYJOjMnazZv0HhVs9QI7j5yt7MrW7De5JK5B2OdWqKKUhtbebW/c1js2B6zj1BSUqP2Zj1znyaFsJu70rJXVuXSiDrMCc6BlWKPXxLaXHkpQaCm9AHFAOpbeQX0Zv1J4UIre5xRz36Q84TnUer2wCPEClJQ2ANmVOgzSClwv90+cSggsJnPnTnNVDhUmhlpoHy/hKv81QeAl1p0pzqXetKJU1F02pSw4CS+oW5r1djR5AidqD3JKyFmrW91iRijBL4JfPIYneU2FIuHhWhQCOMGswTIJ8eXexx9Lm6A3bxYFvHXYtSfgq1GUmehchpgdgSTE/M6DCV+rVEbnpABf2FKS/2D1qRqB81/Wgm+wVOPNQdvBmpTAj22wOMwfwXdinzobvBWoOO6bu6DnnrBf3ZCiPIV/ZjoHAhS31jYTpvr1EiwbaYIHA4tD8t/Tz8yArSVW3n05I+zjdBwwYJxj2vkYLvie2INGp9B1Vd9acm39ARaHjvzxxn2w/KzTGE/q/szpgvAbWnsRO5glK6bk6ZSDVtvuiABBB4q7XxB7gvpYeoC9uGEh0FQacOkFxPi9N6g3uf/F1tPjPfwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Industry               ", type text}, {"Department", type text}, {"Project", type text}, {"Name", type text}})
in
    #"Changed Type"

output:

let
    Source = Table,
    #"Grouped Rows" = Table.Group(Source, {"Industry               ", "Department"}, {{"Data", each _, type table [#"Industry               "=nullable text, Department=nullable text, Project=nullable text, Name=nullable text]}, {"RowCount", each Table.RowCount(_), Int64.Type}, {"DistinctRowCount", each Table.RowCount(Table.Distinct(_)), Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "NewDepartment", each if[DistinctRowCount]<=10 then "Other" else [Department])
in
    #"Added Custom"

PQ output:

Approach VIA DAX:

created a table with the following code:

With newDepartment = 
ADDCOLUMNS(
SUMMARIZE(
    'Table',
    'Table'[Industry               ],
    'Table'[Department],
    "DistinctcountofName", DISTINCTCOUNT('Table'[Name])
),
"NewDepartment", IF( [DistinctcountofName] <=10,"Other",[Department]))

[PoojaG · Accepted solution]

Although the solution provided by @adudani worked for a simple count metric, it didn't for the other 10 calculated complex metrics. Also, the metrics were already created before getting the grouping "others" requirement. So to adjust all the metrics as per new grouping is not an ideal solution. 

What worked for me was, I created a new dataset called Department and Project separately. got an aggregated numbers and created a conditional column new department and new project based on the aggregated numbers. then merged the department and project dataset with the Main detaset and got the new department and project fields. removed the old department and project and replaced them with new fields with the new grouping as "other". This way, I didn't had to change a single metric and it worked perfectly. 

[adudani · Accepted solution]

hi @PoojaG ,

Approach via Power Query below:

create 2  blank queries, Copy and paste the below code into the advanced editor

source data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZXbcuIwDIZfheG6L1GgZWlDoZA9dnohHC1o69iM4qSTt187KbsJNXZaruzosyzpl8XT03ihsrIwXI/6v/HVeIZHYJOjMnazZv0HhVs9QI7j5yt7MrW7De5JK5B2OdWqKKUhtbebW/c1js2B6zj1BSUqP2Zj1znyaFsJu70rJXVuXSiDrMCc6BlWKPXxLaXHkpQaCm9AHFAOpbeQX0Zv1J4UIre5xRz36Q84TnUer2wCPEClJQ2ANmVOgzSClwv90+cSggsJnPnTnNVDhUmhlpoHy/hKv81QeAl1p0pzqXetKJU1F02pSw4CS+oW5r1djR5AidqD3JKyFmrW91iRijBL4JfPIYneU2FIuHhWhQCOMGswTIJ8eXexx9Lm6A3bxYFvHXYtSfgq1GUmehchpgdgSTE/M6DCV+rVEbnpABf2FKS/2D1qRqB81/Wgm+wVOPNQdvBmpTAj22wOMwfwXdinzobvBWoOO6bu6DnnrBf3ZCiPIV/ZjoHAhS31jYTpvr1EiwbaYIHA4tD8t/Tz8yArSVW3n05I+zjdBwwYJxj2vkYLvie2INGp9B1Vd9acm39ARaHjvzxxn2w/KzTGE/q/szpgvAbWnsRO5glK6bk6ZSDVtvuiABBB4q7XxB7gvpYeoC9uGEh0FQacOkFxPi9N6g3uf/F1tPjPfwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Industry               ", type text}, {"Department", type text}, {"Project", type text}, {"Name", type text}})
in
    #"Changed Type"

output:

let
    Source = Table,
    #"Grouped Rows" = Table.Group(Source, {"Industry               ", "Department"}, {{"Data", each _, type table [#"Industry               "=nullable text, Department=nullable text, Project=nullable text, Name=nullable text]}, {"RowCount", each Table.RowCount(_), Int64.Type}, {"DistinctRowCount", each Table.RowCount(Table.Distinct(_)), Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "NewDepartment", each if[DistinctRowCount]<=10 then "Other" else [Department])
in
    #"Added Custom"

PQ output:

Approach VIA DAX:

created a table with the following code:

With newDepartment = 
ADDCOLUMNS(
SUMMARIZE(
    'Table',
    'Table'[Industry               ],
    'Table'[Department],
    "DistinctcountofName", DISTINCTCOUNT('Table'[Name])
),
"NewDepartment", IF( [DistinctcountofName] <=10,"Other",[Department]))

[PoojaG]

@adudani  thank you for your time in providing the solution. Although this works for a simple metric such as count in the sample example, it doesn't or rather get's complicated when I have other ten complicated calculated metrics. I came with a different solution which works for my scenario