Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Service
- Power Query - Group by revenue bands

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

Preetgill93

New Member

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-13-2018
01:54 AM

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 |

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

v-juanli-msft

Community Support

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-13-2018
07:38 PM

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

1 REPLY 1

v-juanli-msft

Community Support

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-13-2018
07:38 PM

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

Announcements

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

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