Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
PoojaG
Helper II
Helper II

Dynamic Other category based on count

I have the following sample data:

 

Industry Department Project            Name

ITRegionalConsultingFiona
ITRegionalConsultingGary
ITRegionalConsultingHelen
ITRegionalCustomer SvcJulia
ITInternationalDevelopmentQuinn
ITInternationalDevelopmentRachel
ITInternationalDevelopmentSam
ITInternationalEngineeringRachel
ITInternationalEngineeringSam
ITInternationalEngineeringTom
ITRegionalConsultingLara
ITRegionalConsultingMia
ITRegionalConsultingRumi
ITRegionalCustomer SvcJake
ITRegionalCustomer SvcLiam
ITRegionalCustomer SvcJordy
ITInternationalDevelopmentTaylor
ITInternationalDevelopmentSwift
ITInternationalDevelopmentMaya
ITGlobalEventsLaura
ITGlobalEventsMike
ITGlobalEventsn Nancy
ITGlobalFinancialKevin
ITGlobalFinancialMark
ITGlobalFinancialMark
ITGlobalLogisticsOscar
ITGlobalLogisticsPatricia
ITGlobalLogisticsQuentin
ITGlobalMarketingAlice
ITGlobalMarketingBob
ITGlobalMarketingCharlie
ITGlobalMarketingDaisy
ITGlobalOperationsCalvin
ITGlobalOperationsDiane
ITGlobalOperationsEdward
ITGlobalProduct DevEthan
ITGlobalProduct DevFiona
ITGlobalProduct DevGabriel
ITGlobalProductionTim
ITGlobalProductionUrsula
ITGlobalProductionVictor
ITLocalResearchNathan
ITLocalResearchOlivia
ITLocalInternLe
ITLocalInternBen
ITLocalResearchPeter
ITLocalSalesWendy
ITLocalSalesXavier
ITLocalSalesZa
ITLocalSalesYvette
ITLocalSalesZoe
ITLocalSalesAaron
ITLocalSalesBella
ITLocalTrainingIsaac
ITLocalTrainingJane
ITLocalTrainingKyle
ITLocalResearchNate
ITLocalResearchLove
ITLocalResearchPete
ITLocalSalesWen
ITLocalSalesXavier
ITLocalSalesTate
ITLocalSalesZoo
ITLocalSalesAaron

 

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

PoojaG_2-1711573372002.png

 

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:

PoojaG_3-1711573446209.png

 

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:

PoojaG_4-1711573511763.png

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!

 

 

 

2 ACCEPTED SOLUTIONS
adudani
Super User
Super User

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:

adudani_0-1711576151223.png



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]))

 

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash

View solution in original post

PoojaG
Helper II
Helper II

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. 

View solution in original post

3 REPLIES 3
PoojaG
Helper II
Helper II

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
Super User
Super User

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:

adudani_0-1711576151223.png



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]))

 

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash

@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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors