Grouping specified columns

**martijnvanderveldt** · 06-26-2015, 10:03 AM

Hi guys!

Probably an easy one but I am working in power BI (designer) and I want to make 5 groups out of 1 row of random data from 1 to 400. I want to create a group from 1 to 10 "Tier 5", 11 to 24 "Tier 4", 25 to 49 "Tier 3", 50 to 99 "Tier2" and 100< "Tier 1".

Now ofcourse I know we can do this with IF function. But this is a long formula and I will have to do similar groupings more often for different columns. So now I was wondering whether this process can't be made easier with maybe a DAX formula. Maybe it would be easiest doing it by creating a relationship with another column created by ourselves. But any input is great as long as it is an easy one to use!

Thanks!

Marty V!

**Special-K** · 06-26-2015, 10:07 AM

Something along lines of

="Tier "&LOOKUP(A1,{1,11,25,50,100},{5,4,3,2,1})

First part in brackets is the lower end of your ranges, second part in brackets is the result given A1

**martijnvanderveldt** · 06-26-2015, 10:57 AM

Thank you very much it works in excel! In power Pivot however it doesn't seem to work (Works with DAX). Should I change the formula perhaps?

And what if I want to do the same with percentages.
10% =Tier 4, next 20 %= Tier 3, Next 30%= Tier 2 and final 40% =tier 1?

Thank you very much for your help!