Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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,
Solved! Go to 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.
[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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |