Conditional Column not quite correct

[mattb1990]

Hi,

I was hoping someone will be able to help me with this issue. 

I have a list of Clients and a list of the number of deals we have done with each client. I want to categorise these clients in to Bronze, Silver, Gold and Platinum tiers based on the number of deals. 

I have created a conditional column as so:

For the most part this is working correctly, however, there is an issue where a client overall crosses one of these tiers, but deals have been done by two different teams meaning the value for each is below the threshold. For example see below:

Where we have done 11 deals with the highlighted client, they should be showing as Gold, however, these deals are split 6 & 5 across two teams and show it is showing as Silver

The team column is not present in this table so I would like this to be summarised to show Gold, but split to show Silver and Silver for each team when I add in the Team column. 

Is there a way around this? any help would be much appreciated!

Thank you 

[Anonymous]

Hi @mattb1990 

You can refer to the following example:

Create a custom column in power query

if [Number of Runners] >= 5 and [Number of Runners] < 10 then "Silver" else if [Number of Runners] >= 10 and [Number of Runners] < 20 then "Gold" else if [Number of Runners] >= 20 then "Platinum" else "Bronze"

 Output:

You need to put interval to the condition, such as [5,10] [10,20]

Best Regards!

Yolo Zhu

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

[serpiva64]

Hi,

i think you have to create 2 tier

then it depends on your visualizations