Forum Discussion

shday98's avatar
shday98
Regular Visitor
3 years ago

Flagging Less Than Ideal Training Hours Accumulation

Good evening Microsoft Power BI Community!

 

I'm stuck with a problem I'm hoping you can help with... I have a table that is a grouped sum of the training hours team members have accumulated through the year. 

 

I've also added a ficticious team member called "Ideal" in the table that takes the target number of training hours for the year and divides it by 12 for each month, and this represents on a graph the "straight-line accumulation of training hours".

 

What I'm trying to do is in the aggregation table add another column with logic flagging it as TRUE (above the ideal accumulation) or FALSE (below the ideal accumulation) but I don't know how to do it right so it's dynamic.

 

NameHours Accumulated
George52
Fred40
Marianne35
Ideal46.67

 

The idea is that if I could get the logic right I'd have a table that read:

NameHours AccumulatedAbove Ideal
George52TRUE
Fred40FALSE
Marianne35FALSE

 

I tried using reference to create another query/table that only held the ideal accumulation

46.67

 

...and I even tried changing that value to a list value... but none of it worked/I couldn't figure out how to dynamically take "IDEAL"'s hours accumulation and flag the rest of the users as TRUE (greater than IDEAL's accumulation) or FALSE (less than IDEAL's accumulation) so that the managers of these resources would have a short-list of employees to follow-up with.

 

I hope this makes enough sense such that you can help me with a solution!

 

Thanks in advance!

Need Help

1 Reply

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    3 years ago

    Use following formula in a custom column where you would need to replace #"Changed Type" with your previous step name

     

     

    [Hours Accumulated]>List.Last(#"Changed Type"[Hours Accumulated]​

     

    After this step filter out Ideal row.

    A sample code

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck/NL0pPVdJRMjVSitWJVnIrSk0B8kwMwDzfxKLMxLw8kLyxKVjEMyU1MQekwEzPzFwpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Hours Accumulated" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Hours Accumulated", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Name]="Ideal" then null else [Hours Accumulated]>List.Last(#"Changed Type"[Hours Accumulated])),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Name] <> "Ideal"))
in
    #"Filtered Rows"