DAX to Group Hours after Aggregation has occured

[DebbieE]

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?

[v-frfei-msft · Accepted 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

[DebbieE]

Fantastic, Thank you so much.

I referenced my original table and then grouped on this one