Order buckets using Analysis Services

[Anonymous]

Hello Community,

In Power BI, I want to create a column chart with the count of awarded orders on the x-axis, and the count of suppliers on the y-axis. The count of orders should be presented as buckets, so grouped in 1-5, 6-20, 21-50 etc. I have a live connection to an Analysis Services model for which I use Visual Studio. In here, there is a Purchase Orders table and a Supplier table, linked by Supplier ID.

I have found below link that describes about the same but for customer orders:

https://radacad.com/customers-grouped-by-count-of-their-orders-static-segmentation-in-power-bi

This allows me to select the counts on both axes. However, I can't figure out how to present the orders in the bucket segments. 

I would appreciate any help on how to achieve this using an AS model in Visual Studio as a source, as PBI does not allow many functionalities when using a live connection. 

Thanks in advance,

Thomas

[amitchandak]

@Anonymous , In case of analysis service you can not do static segmentation.  Also for binning you need one static table for the bucket. unless you have that source you will not be able to do that

https://www.daxpatterns.com/dynamic-segmentation/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

[lkalawski · Accepted solution]

@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]

@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

[lkalawski]

Hi @Anonymous

You cannot insert a measure in the x-axis field - it must be a regular column or a calculated column. Are count orders in the column or are you taking them as a measure?

You could send sample data for both tables. I will help you create a chart.

_______________
If I helped, please accept the solution and give kudos! 😀

[vanessafvg]

unfortunately i do not think you will be able to do this in a live connection as you will need calculated columns for the buckets, dont think you can do that in a live connection. All the modelling is pushed to the SSAS model except for measures.