Distribution of one measure by another - pbix and dax.do code provided

[AhsenMajid]

I am using the Contoso db (dax.do).

There is a sales table, which is a fact table. 

There are also customer, product, and order dimension tables. 

I want to see the distribution of the count of distinct product keys by order number. Do most orders have just one product key? 5? etc. (sharing PBIX https://we.tl/t-ynvzHDkXIv and code at https://dax.do/yFMaeAbs5BRRWr/ - data is slightly different, problem and result is the same) 

This is the solution in SQL (copied the DB over using DAX Studio). 

This is what I get with DAX:

You can see the intermediate table is actually fine, and the number of orders with 7 distinct product keys is 45 (see row num on tabled, ordered dec.). This is exactly the same as what SQL give us. 

Question:

1. How can I get the "no of sales orders" column correct, given I have the intermediate table correct?
2. Once I've got the the correct result in a table expression, how do I get one in a visual?

Thanks!

DEFINE
MEASURE 'Sales'[Distinct Product Key Count] = DISTINCTCOUNT( 'Sales'[ProductKey] )

EVALUATE

VAR __tbl_count_product_keys_by_order_number = 
ADDCOLUMNS (
	DISTINCT ( 'Sales'[Order Number] ),
	"@Distinct Product Keys", [Distinct Product Key Count]
)

VAR __tbl_distinct_column_number_of_product_keys = 
	DISTINCT ( 
		SELECTCOLUMNS ( 
			__tbl_count_product_keys_by_order_number,
			"@No of Distinct Product Keys", [@Distinct Product Keys]
		)
	)
	
VAR result = 
ADDCOLUMNS(
	SUMMARIZE(
		__tbl_count_product_keys_by_order_number,
		[@Distinct Product Keys]
	),
	"no of sales orders", 
	CALCULATE( 
		COUNTROWS( __tbl_distinct_column_number_of_product_keys )
	)
)

// RETURN result ORDER BY [@Distinct Product Keys]

RETURN __tbl_count_product_keys_by_order_number ORDER BY [@Distinct Product Keys] DESC

[ThxAlot · Accepted solution]

You're looking to calculate distribution of _count_product_keys_by_order_number, that's to say, _count_product_keys_by_order_number is a dimension; therefore it's no long a calculation on the fly, but a tangible (calculated) column.

First create a calculated table in table view as what you did in __tbl_count_product_keys_by_order_number; then put necessary column in a viz.