Forum Discussion
Order buckets using Analysis Services
Anonymous
I checked again and you can do this in this way:
Given this data structure:
you need to create a calculated table using this DAX code:
Customer Orders = SUMMARIZE( 'Table', 'Table'[SupplierID], "Count of Orders", COUNTROWS(VALUES('Table'[OrderID])) )You will receive calculations of how many orders each supplier had. Then we will create groups (buckets):
Buckets = SWITCH(TRUE(), 'Customer Orders'[Count of Orders] < 2, "<2", 'Customer Orders'[Count of Orders] >= 2 && 'Customer Orders'[Count of Orders] <= 5, "2-5", ">5")Thanks to this, you will get groups that you can use in the chart. Put Buckets on the x-axis, and add SupplierID as Value and change it to Count, or write measure for Count for Suppliet ID and put it in Value field.
If you have any problems, ask.
_______________
If I helped, please accept the solution and give kudos! 😀
Anonymous
I checked again and you can do this in this way:
Given this data structure:
you need to create a calculated table using this DAX code:
Customer Orders =
SUMMARIZE(
'Table',
'Table'[SupplierID],
"Count of Orders",
COUNTROWS(VALUES('Table'[OrderID]))
)
You will receive calculations of how many orders each supplier had. Then we will create groups (buckets):
Buckets =
SWITCH(TRUE(),
'Customer Orders'[Count of Orders] < 2, "<2",
'Customer Orders'[Count of Orders] >= 2 && 'Customer Orders'[Count of Orders] <= 5, "2-5",
">5")
Thanks to this, you will get groups that you can use in the chart. Put Buckets on the x-axis, and add SupplierID as Value and change it to Count, or write measure for Count for Suppliet ID and put it in Value field.
If you have any problems, ask.
_______________
If I helped, please accept the solution and give kudos! 😀
lkalawski Thanks a lot for your detailed reply! Visual Studio freaked out when I tried to add the buckets as a column in the calculated table, so I tried it the other way described here: https://radacad.com/customers-grouped-by-count-of-their-orders-static-segmentation-in-power-bi --> Section 'Grouping in Power Query'
With your help, I was then able to add the Buckets column and create the chart.
Thanks again!
Kind regards,
Thomas