cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Count number of Items per Order

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:

mg_2000_0-1625230696082.png

 

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, 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

[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.

View solution in original post

1 REPLY 1
Anonymous
Not applicable

[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.

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 episode 5 with aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt will talk about the importance of Data Modeling.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!