Forum Discussion

mattb1990's avatar
mattb1990
Frequent Visitor
3 years ago

Conditional Column not quite correct

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 

 

 

 

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.