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.
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!
Solved! Go to 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]))
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.
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.
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]))
@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
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 |
---|---|
105 | |
96 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |