Count number of Items per Order

[Anonymous]

Hi!

My data-set consists of order rows with order_numers including one or multiple products. I would like to count number of products (items) in each order. I get it to work on total, which means I include all item and are not using any filters. The formula I have used for that is the following: 

Items per Order =

CALCULATE(COUNT(SALES[product_key]),

ALLEXCEPT(SALES, SALES[order_number]))

What I now want to get help with is to calculate above but only include product_category = B. 

Expected outcome:

You find some sample data through this link:
https://drive.google.com/file/d/1fIlcHbCpBfh1b-ajGMdmNJBS7Nru4VTY/view?usp=sharing

Please, let me know if any questions - Happy for any support I can get!

Thanks, 

[Anonymous · Accepted solution]

[Items per Order (B cat.)] =
var CurrentOrderNumber = Sales[order_number]
return
    COUNTX(
        filter(
            Sales,
            Sales[order_number] = CurrentOrderNumber
            &&
            RELATED( Products[product_category] ) = "B"
        ),
        Sales[product_key]
    )

In calculated columns in fact tables you should never use CALCULATE.