cancel
Showing results for
Did you mean:
Highlighted
New Member

## Power Query - Group by revenue bands

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.

 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
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted Community Support

## Re: Power Query - Group by revenue bands

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

Highlighted Community Support

## Re: Power Query - Group by revenue bands

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

Announcements #### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge! #### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members. #### Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community. Top Solution Authors
Top Kudoed Authors
Users online (1,711)