I wanted to create COUNT(DISTINCT "Orders"."Order Number" by "Products"."Product Type") as a column in the repository, based on the default Sample Sales.
In the Sample Sales there is already a column that return COUNT(DISTINCT "Orders"."Order Number"). It's Name is # of orders and it is defined as "Count Distinct" aggregation on the Order_number key:
Basically the COUNT(DISTINCT "Orders"."Order Number" by "Products"."Product Type") is equivalent to Sum("# of Orders" by "Products"."Product Type"). For example:
I will use this logic in the RPD:
I duplicated the # of Orders column. In our case the aggregation by Products dimension should be sum, while by other dimension "Count Distinct", so turn on the "Based on dimensions" option and set it (use the Plus sign to add Logical Dimensions and select the formula from the pull down menu):
Now the last part is to set the levels, at the column to be by Product Type for products and Total for all the rest:
I didn't bother with column name so it's "# of Orders 1".
This is the result:
