Group Orders by Quantity of Item ordered

[WCONGRAVE]

HI

I have a Star Schema Model, with all of the data held in the fact table linked to dimension tables such as customer, calendar, product etc.

Now I have a requirement to report based upon how many items are in an order as per below

Items in Order	Count of Orders	Total £
1	1000	£10000
2	12000	£260000
3	14000	£450000
4	15000	£666000
5	13000	£845000

In my mind, to do this I'd need a Sales Header table with the count of lines in it per order to act as a DIM table. I could then join on the transaction ID and  produce the above report. But my fact table has tens of millions of rows, so calculating this table would take a long time, and the join would be really inefficient. Is there a better way of acheiving this same result in a DAX calculation?

[v-yiruan-msft]

Hi @WCONGRAVE ,

Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem with you. Thank you.

Best Regards

[v-yiruan-msft · Accepted solution]

Hi @WCONGRAVE ,

In order to provide you a suitable solution, could you please share some sample data in your model (exclude sensitive data) and your expected result with calculation logic and special examples? Thank you.

In addition, you can refer the following links to get it.

How to group customers by count of their orders and show 0 (i.e., customers that did not order)?

Customers Grouped by Count of Their Orders – Static Segmentation in Power BI

Best Regards

[amitchandak]

@WCONGRAVE , usually we merge and create single table for sales header and sales transactions

we can count distinct for sales header

or measure like

sumx(summarize(sales, sales[header id], sales[header amount]), [header amount] )