I'm at a blank. In PowerBI I want to be able to take my data, group it into quartiles and create a bar chart similar to below. I can do it in Excel, but have no idea how to do it in PowerBI. Any ideas how to do this?
Customer ID Total Sales ($)
| 22791 | 47777.18 | ||
| 22795 | 38421.04 | ||
| 22792 | 6000 | ||
| 22791 | 6000 | ||
| 22793 | 22003.75 | ||
| 22797 | 44695.44 | ||
| 22793 | 27615 | ||
| 22795 | 2196 | ||
| 22792 | 6000 | ||
| 22791 | 6000 | ||
| 22793 | 29899.65 | ||
| 22793 | 34190.25 | ||
| 22797 | 33441 | ||
| 22795 | 2195 | ||
| 22792 | 6000 | ||
| 22791 | 6000 | ||
| 22797 | 34836.63 | ||
| 22793 | 23979 | ||
| 22795 | 1947 | ||
| 22792 | 6000 | ||
| 22791 | 6000 | ||
| 22795 | 2235 | ||
| 22793 | 29286.68 | ||
| 22792 | 6000 | ||
| 22791 | 6000 | ||
| 22795 | 1885 | ||
| 22793 | 20205 | ||
| 22793 | 18045 | ||
| 22792 | 6000 | ||
| 22791 | 6000 | ||
| 22795 | 2495 | ||
| 22792 | 6000 | ||
| 22791 | 6000 | ||
| 22795 | 2745 | ||
| 22793 | 17385 | ||
| 22793 | 18715.5 | ||
| 22795 | 2675 | ||
| 22792 | 6000 | ||
| 22791 | 6000 | ||
| 22792 | 6000 | ||
| 22791 | 6000 | ||
| 22795 | 2558 | ||
| 22794 | 1906.82 | ||
| 22793 | 20083.02 | ||
| 22792 | 6000 | ||
| 22791 | 6000 | ||
| 22794 | 6843.34 | ||
| 22795 | 2773 | ||
| 22793 | 26935.52 | ||
| 22792 | 6000 | ||
| 22791 | 6000 | ||
| 22795 | 0 | ||
| 22794 | 6898.12 | ||
| 22793 | 20202.5 | ||
| 22792 | 6000 | ||
| 22791 | 6000 | ||
| 22794 | 5857.77 | ||
| 22795 | 2675 | ||
| 22793 | 32338.25 | ||
| 22792 | 6000 | ||
| 22791 | 6000 | ||
| 22795 | 3885 | ||
| 22794 | 2582.8 | ||
| 22792 | 6000 | ||
| 22791 | 6000 | ||
| 22793 | 21330 | ||
| 22794 | 7977.2 | ||
| 22795 | 0 | ||
| 22793 | 21011.5 | ||
| 22792 | 6000 | ||
| 22791 | 6000 | ||
| 22795 | 3125 | ||
| 22794 | 17494.26 | ||
| 22792 | 6000 | ||
| 22791 | 6000 | ||
| 22794 | 1970.85 |
1 ACCEPTED SOLUTION
Hi @Sirhawk3017 ,
@amitchandak Thanks for your reply!
And @Sirhawk3017 , have you solved your problem? If you have solved, please accept @amitchandak 's answer as solution.
And if you have not solved, you can try this way:
Add an Index column in Power Query:
Close & Apply and in Power BI Desktop, create new group for Index:
Then you will get a column like this:
Use this DAX to create a calculated column:
X-axis =
VAR _a = (COUNTROWS('Table') - 1) / 4
RETURN
SWITCH(
TRUE(),
'Table'[Index (bins)] = 1, "Quartiles 1",
'Table'[Index (bins)] = 1 + _a, "Quartiles 2",
'Table'[Index (bins)] = 1 + 2*_a, "Quartiles 3",
"Quartiles 4"
)
And use this DAX to create a measure:
Measure =
CALCULATE(
SUM('Table'[Total Sales ($)]),
ALLEXCEPT('Table', 'Table'[X-axis])
)Then you can create the visual like this:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I will give this a shot tonight! thank you so much for the reply. I'll update if this was the fix.
Hi @Sirhawk3017 ,
@amitchandak Thanks for your reply!
And @Sirhawk3017 , have you solved your problem? If you have solved, please accept @amitchandak 's answer as solution.
And if you have not solved, you can try this way:
Add an Index column in Power Query:
Close & Apply and in Power BI Desktop, create new group for Index:
Then you will get a column like this:
Use this DAX to create a calculated column:
X-axis =
VAR _a = (COUNTROWS('Table') - 1) / 4
RETURN
SWITCH(
TRUE(),
'Table'[Index (bins)] = 1, "Quartiles 1",
'Table'[Index (bins)] = 1 + _a, "Quartiles 2",
'Table'[Index (bins)] = 1 + 2*_a, "Quartiles 3",
"Quartiles 4"
)And use this DAX to create a measure:
Measure =
CALCULATE(
SUM('Table'[Total Sales ($)]),
ALLEXCEPT('Table', 'Table'[X-axis])
)Then you can create the visual like this:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Sirhawk3017 , Check this ABC analysis. I think you can create 4 using .25, .5, .75 and 1
Power BI ABC Analysis using Window function, Dynamic Segmentation: https://youtu.be/A8mQND2xSR4
