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 data like this
OrgID Event Hours
1 1 4
1 2 4
2 1 1
3 1 0
4 1 5
5 1 7
1 3 4
2 2 2
3 2 1
And I want to group on Hours After Ive counted all the distinct organisations. So for example
OrgID Hours Group
1 12 12 or Over
2 3 0 to 3 hours
3 2 0 to 3 hours
4 5 3 to 6 houur
5 7 6 to 12 hours
And then you could have a column chart showing the number of of organisations against each hour.
But I just cant find an easy way to do it. I tried this DAX
EngagedHours = IF(SUM(Activity[Hours]) >= 0.5 && SUM(Activity[Hours]) < 6, "Number of 0.5 to 6 hours", IF(SUM(Activity[Hours]) >= 6 && SUM(Activity[Hours]) < 9, "Number on 6 to 9 hours", IF(SUM(Activity[Hours]) >= 9 && SUM(Activity[Hours]) < 12, "Number on 9 to 11.9 hours", IF(SUM(Activity[Hours]) >= 12,"Number of 12 hours or above", "NA") )))
But it just doesnt work when I take the OrgIDs out. It just shows me everything is over 12 hours. I didnt do a simple conditional column in Power Query because the rows have to be aggregated to the total hours for each org. It cant be done by row. Is there any way of doing this fairly easily?
Solved! Go to Solution.
Hi @DebbieE ,
1. Group by the OrgID column in power query.
2.Add a custom column by this way.
Custom = if[Count]>=0.5 and [Count]<6 then "Number of 0.5 to 6 hours" else if[Count]>=6 and [Count]<9 then "Number on 6 to 9 hours" else if [Count] >=9 and [Count]<12 then "Number on 9 to 11.9 hours" else if [Count]>=12 then "Number of 12 hours or above" else "NA"
Also please refer to the M code as below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAWMTpVgdCM8IzjOCyhmCecZQngGYZwLlmYJ5plCeOdwUYxRTIBhmCsTc2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [OrgID = _t, Event = _t, Hours = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"OrgID", Int64.Type}, {"Event", Int64.Type}, {"Hours", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"OrgID"}, {{"Count", each List.Sum([Hours]), type number}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each if[Count]>=0.5 and [Count]<6 then "Number of 0.5 to 6 hours" else if[Count]>=6 and [Count]<9 then "Number on 6 to 9 hours" else if [Count] >=9 and [Count]<12 then "Number on 9 to 11.9 hours" else if [Count]>=12 then "Number of 12 hours or above" else "NA") in #"Added Custom"
For more details, please check the pbix as attached.
Regards,
Frank
Hi @DebbieE ,
1. Group by the OrgID column in power query.
2.Add a custom column by this way.
Custom = if[Count]>=0.5 and [Count]<6 then "Number of 0.5 to 6 hours" else if[Count]>=6 and [Count]<9 then "Number on 6 to 9 hours" else if [Count] >=9 and [Count]<12 then "Number on 9 to 11.9 hours" else if [Count]>=12 then "Number of 12 hours or above" else "NA"
Also please refer to the M code as below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAWMTpVgdCM8IzjOCyhmCecZQngGYZwLlmYJ5plCeOdwUYxRTIBhmCsTc2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [OrgID = _t, Event = _t, Hours = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"OrgID", Int64.Type}, {"Event", Int64.Type}, {"Hours", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"OrgID"}, {{"Count", each List.Sum([Hours]), type number}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each if[Count]>=0.5 and [Count]<6 then "Number of 0.5 to 6 hours" else if[Count]>=6 and [Count]<9 then "Number on 6 to 9 hours" else if [Count] >=9 and [Count]<12 then "Number on 9 to 11.9 hours" else if [Count]>=12 then "Number of 12 hours or above" else "NA") in #"Added Custom"
For more details, please check the pbix as attached.
Regards,
Frank
Fantastic, Thank you so much.
I referenced my original table and then grouped on this one
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |