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.
Hey,
I have I believe a pretty simple request, I unfortunately cannot seem to figure it out. For the below table created from my dataset set I want to count for each unique order nr determine how many items were purchased total. However everytime I do a formula where Line qty is greater than 1 it returns orders which have a line qty of the value of 2 rather than the sum.
Example: order 10000 has 3 unique items with a line quantity of 1 for a sum total of 3. while another order has 1 unique item with a line qty of 2 for a sum total of 2. The below formula keep returning the latter condition and not the former.
Solved! Go to Solution.
Hi @stino123 ,
When there are other groupings in the table, it will have an effect on the results of the grouping statistics. You can create a dummy table and then count the total number of rows with orders greater than 1.
t_ = SUMMARIZE('Table','Table'[Order],'Table'[Col_order2])
Measure = CALCULATE(COUNTROWS(t_),t_[Col_order2]>1)
If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your feedback.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @stino123 ,
When there are other groupings in the table, it will have an effect on the results of the grouping statistics. You can create a dummy table and then count the total number of rows with orders greater than 1.
t_ = SUMMARIZE('Table','Table'[Order],'Table'[Col_order2])
Measure = CALCULATE(COUNTROWS(t_),t_[Col_order2]>1)
If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your feedback.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for the quick reply. Not quiet, now I need to count how many distinct order number have a sum line quantity greater than two.
Sum if line quantity =
CALCULATE ( DISTINCTCOUNT ( FACT_Orders[Order Nr] ), FACT_Orders[Line QTY] > 2 )
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hey @Fowmy, this formula shows a condition where line quantity is greater than two. What Is needed if the amount of unique lines for a unique order is greater than two. as you see below in the raw data this order is greater than 2 however the number of lines is 1
@stino123
Your order numbers are already grouped so you can use a simple measure. Maybe I got it wrong.
Sum if line quantity = SUM(FACT_Orders[Line QTY])
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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 |
---|---|
13 | |
6 | |
4 | |
3 | |
2 |
User | Count |
---|---|
14 | |
10 | |
5 | |
3 | |
3 |