Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.