In my data model I have a group of orders.  I arrive at the count of distinct orders
with:

Order Count:=COUNTROWS(DISTINCT('all sales data'[Order Number]))

Result is 10,576

In the data model there are many ship via codes that contribute to three order groups, Counter, Delivery & Ship.  I have a related column in the 'all sales data' table that displays on of those three order groups.

I am needing the following order counts (by those order groups):

• Counter Order      Count
• Delivery Order      Count
• Ship Order Count

My assumption is that I need to add FILTER to my formula so the COUNTROWS(DISTINCT( keys on

all sales data[Order Group]=Counter

Any help you can provide would be appreciated.

Thanks in advance,

Scott

 

 

If it's all in one table "all sales data" you should be able to just create a pivottable/PowerView table that displays your order groups and then add your measure and it should automagically filter the order counts by the table rows.

• Edited by Seth Moupre 12 hours 21 minutes ago

Yes, that's true.  But I still have some other formulas I want to use in the background based on the order group count.

Counter Count:=COUNTROWS(DISTINCT(FILTER('all sales data'[Order Number],all sales data[Order Group]=Counter)))

I didn't test that but that should be the correct formula, FILTER brings back order numbers that have an order group of "Counter" and you get the distinct values for that (works because you only bring back one column and you count the rows.

Obviously, create your other 2 measures similarly.

Hmm, on second thought you probably need a CALCULATE in there:

Counter Count:=COUNTROWS(DISTINCT(CALCULATE([Order Number],FILTER('all sales data',all sales data[Order Group]=Counter))))

• Proposed as answer by Seth Moupre 10 hours 48 minutes ago
• Edited by Seth Moupre 10 hours 45 minutes ago