Forum Discussion
Solved
Orders with one product
Hi, I'm beginner in Power Bi and Dax so i think its very simple case, but it's struggle me for couple days.
I have table orders which looks like that (of course there are a lot of others columns, but i think not important for this case)
| product id | order numer | quantity of sales |
| 1 | 1 | 1 |
| 1 | 2 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 10 |
For each product I want to calculate two measures:
- (A) all orders with that product
- (B) orders where is only one id product
So in results I would like to get that table:
| id product | (A) numer of orders with product | (B) number of orders only with that product |
| 1 | 2 | 1 |
| 2 | 1 | 0 |
| 3 | 1 | 1 |
You can create a couple of measures like
Num orders only that product = VAR OrdersAndNumProducts = ADDCOLUMNS ( VALUES ( 'Orders'[Order num] ), "@num products", CALCULATE ( DISTINCTCOUNT ( 'Orders'[Product ID] ), ALLEXCEPT ( 'Orders', 'Orders'[Order num] ) ) ) VAR Result = COUNTROWS ( FILTER ( OrdersAndNumProducts, [@num products] = 1 ) ) RETURN ResultNum orders = COUNTROWS( 'Orders' )
2 Replies
johnt75Super User
You can create a couple of measures like
Num orders only that product = VAR OrdersAndNumProducts = ADDCOLUMNS ( VALUES ( 'Orders'[Order num] ), "@num products", CALCULATE ( DISTINCTCOUNT ( 'Orders'[Product ID] ), ALLEXCEPT ( 'Orders', 'Orders'[Order num] ) ) ) VAR Result = COUNTROWS ( FILTER ( OrdersAndNumProducts, [@num products] = 1 ) ) RETURN ResultNum orders = COUNTROWS( 'Orders' )It works, thank You 🙂
