Power Query - Group by revenue bands

[Preetgill93]

Hi team,

I am fairly new to the world of Power Query and Power Pivot. I have set up a Data Model and am using Power Pivot to play around with the presentation of the data. I would like to be able to group profit data by numerical ranges as per the table below.

How best can I achieve this? I have read that I may need to introduce a measure but I need a little bit more direction.

Thanks in advance!

Range	Min	Max
-$40-$30	-40	-30
-$30-$20	-30	-20
-$20-$10	-20	-10
-$10-$5	-10	-5
-$5-$0	-5	0
$0-$10	0	10
$10-$20	10	20
$20-$30	20	30
$30-$40	30	40
$40-$50	40	50
$50-$100	50	100
$100-$200	100	200
$200-$300	200	300

[v-juanli-msft · Accepted solution]

Hi @Preetgill93

As my understanding, you have a column like [profit], you need to group data in the column by the ranges.

You could create a measure or a calculated column

measure =
SWITCH (
    TRUE (),
    MAX ( [profit] ) <= -30
        && MAX ( [profit] ) >= -40, "-$40-$30",
    MAX ( [profit] ) <= -20
        && MAX ( [profit] ) > -30, "-$30-$20",
    MAX ( [profit] ) <= -10
        && MAX ( [profit] ) > -20, "-$20-$10",
    MAX ( [profit] ) <= -5
        && MAX ( [profit] ) > -10, "-$10-$5",
    MAX ( [profit] ) <= 0
        && MAX ( [profit] ) > -5, "-$5-$0",
    MAX ( [profit] ) <= 10
        && MAX ( [profit] ) > 0, "$0-$10",
    MAX ( [profit] ) <= 20
        && MAX ( [profit] ) > 10, "$10-$20",
    MAX ( [profit] ) <= 30
        && MAX ( [profit] ) > 20, "$20-$30",
    MAX ( [profit] ) <= 40
        && MAX ( [profit] ) > 30, "$30-$40",
    MAX ( [profit] ) <= 50
        && MAX ( [profit] ) > 40, "$40-$50",
    MAX ( [profit] ) <= 100
        && MAX ( [profit] ) > 50, "$50-$100",
    MAX ( [profit] ) <= 200
        && MAX ( [profit] ) > 100, "$100-$200",
    MAX ( [profit] ) <= 300
        && MAX ( [profit] ) > 200, "$200-$300"
)

Column =
SWITCH (
    TRUE (),
    [profit] <= -30
        && [profit] >= -40, "-$40-$30",
    [profit] <= -20
        && [profit] > -30, "-$30-$20",
    [profit] <= -10
        && [profit] > -20, "-$20-$10",
    [profit] <= -5
        && [profit] > -10, "-$10-$5",
    [profit] <= 0
        && [profit] > -5, "-$5-$0",
    [profit] <= 10
        && [profit] > 0, "$0-$10",
    [profit] <= 20
        && [profit] > 10, "$10-$20",
    [profit] <= 30
        && [profit] > 20, "$20-$30",
    [profit] <= 40
        && [profit] > 30, "$30-$40",
    [profit] <= 50
        && [profit] > 40, "$40-$50",
    [profit] <= 100
        && [profit] > 50, "$50-$100",
    [profit] <= 200
        && [profit] > 100, "$100-$200",
    [profit] <= 300
        && [profit] > 200, "$200-$300"
)

Best Regards

Maggie