How to do a count of count without aggregation

[oli6]

Hi,

  Here is my problem.

  I have this simple table below (Customer code and associated Order code) : 

Customer #	Order #
C1	O1
C1	O2
C2	O3
C3	O4
C3	O5
C3	O6
C3	O7
C4	O8

From this, i want to calculate this : 

Number of customers	Number of orders
2	1
1	2
1	4

 In other words : we have 2 customers that have done only 1 order, only 1 customer with 2 orders and only 1 customer with 4 orders.

 And this will have to work with all dynamic filters in the report.

 Any idea on how to do this ?

Thanks !

[oli6]

Hi Jonht75,

  Thank you very much for your answer, that works nicely.

  Initialy, i wanted to generate automatically the table below : 

Number of customers	Number of orders
2	1
1	2
1	4

Do you think it is possible ?

[johnt75]

That's the table which is generated as the GroupedTable variable in my code, but I don't see a way to get that into a table in a report.

[oli6]

Yes, that's my problem since the beginning. The request looks simple but it is not...

I was able to acheive this with calculated table, but it's generating a static table that will not work with filters in the document.

Thanks for you help anyway.

[johnt75 · Accepted solution]

The method I propose is dynamic and will work with any filters, slicers etc. The only static thing is the generated table for the numbers of orders, but as long as you create that to far exceed the possible number of orders then it shouldn't be a problem.

[johnt75]

You could create a table to hold the possible numbers of orders just using GENERATESERIES and then put that in a visual with a measure like

Num customers by num orders = 
VAR SummaryTable =
	ADDCOLUMNS(
		SUMMARIZE( 'Table', 'Table'[Customer #] ),
		"@Num orders", [Num Orders]
	)
VAR GroupedTable =
	GROUPBY(
		SummaryTable,
		[@Num orders],
		"Num cust", SUMX( CURRENTGROUP( ), 1 )
	)
RETURN
	SELECTCOLUMNS(
		FILTER(
			GroupedTable,
			[@Num orders] = SELECTEDVALUE( 'Numbers of orders'[Value] )
		),
		"Num cust", [Num cust]
	)