Trying to create buckets of percentages.. getting duplicates

[exf048s]

I am trying to calculate "ontime%" for my clients and bucket them in 5 groups. Example is 0-64% ,65-74%, 75-84%, 85-89% and 90%+. I created a new column with the below formula. For some reason it is now duplicating my client names and putting them in multipul buckets. Any thoughts on why? 

Column- On time percent = if(' query'[Ontime_Cases] = 0, "0-64%",

if('query'[Ontime_Cases]/' query'[Received_Cases]<.65, "0-64%",

if('query'[Ontime_Cases]/' query'[Received_Cases] <.75, "65% to 74%",

if('query'[Ontime_Cases]/' query'[Received_Cases] <.85, "75% to 84%",

if('query'[Ontime_Cases]/' query'[Received_Cases] <.90, "85% to 89%",

if('query'[Ontime_Cases]/' query'[Received_Cases]>.89, "90+%","0-64%"))))))

[lbendlin · Accepted solution]

please read about the SWITCH() statement in DAX.

[Anonymous · Accepted solution]

Hi @exf048s ,

@lbendlin @samratpbi Thanks for your concern about this case and Good Answer! And I agree with @lbendlin that it would be more convenient to use SWITCH!
And this is Power Query forum, if you want to get answer by using DAX, you can post for help in the DAX or Desktop forums. And if you want to achieve your expected result by using Power Query, you can try this solution:
First, add such a custom column:

[Ontime_Cases] / [Received_Cases]

Then add the expected custom column:

if [Percentage] < 0.65 then "0% to 64%" else if [Percentage] >= 0.65 and [Percentage] < 0.75 then "65% to 74%" else if [Percentage] >= 0.75 and [Percentage] < 0.85 then "75% to 84%" else if [Percentage] >= 0.85 and [Percentage] < 0.9 then "85% to 89%" else "90+%"

Here is the whole M function in the Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUTI0UIrViVYy1TNFcCz0TBAcMz1jBMdEzwjBMUTSb4iqH0mLuZ4hlBMLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ontime_Cases = _t, Received_Cases = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ontime_Cases", type number}, {"Received_Cases", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Percentage", each [Ontime_Cases] / [Received_Cases]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Percentage", Percentage.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom", each if [Percentage] < 0.65 then "0% to 64%" else if [Percentage] >= 0.65 and [Percentage] < 0.75 then "65% to 74%" else if [Percentage] >= 0.75 and [Percentage] < 0.85 then "75% to 84%" else if [Percentage] >= 0.85 and [Percentage] < 0.9 then "85% to 89%" else "90+%")
in
    #"Added Custom1"

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin · Accepted solution]

please read about the SWITCH() statement in DAX.

[samratpbi]

Hi, are you using Power Query? Then you may create a conditional column which us under Add Columns. If you are using conditional column then it should work properly. You may use both greater than and less than condition ( like if('query'[Ontime_Cases]/' query'[Received_Cases] >= .65 && 'query'[Ontime_Cases]/' query'[Received_Cases] <.75, "65% to 74%",) If this still doesnt work, can you please provide more details about the data?

This this resolves your problem then mark it as Solutions, thanks