cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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.

 

Thanks in advance!

 

RangeMinMax
-$40-$30-40-30
-$30-$20-30-20
-$20-$10-20-10
-$10-$5-10-5
-$5-$0-50
$0-$10010
$10-$201020
$20-$302030
$30-$403040
$40-$504050
$50-$10050100
$100-$200100200
$200-$300200300
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Power Query - Group by revenue bands

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

1.png

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

View solution in original post

1 REPLY 1
Highlighted
Community Support
Community Support

Re: Power Query - Group by revenue bands

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

1.png

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

View solution in original post

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

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

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Community Summit Australia – Join Online!

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