Sumifs in power bi

[stino123]

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.

Sum if line quantity = CALCULATE(SUM(FACT_Orders[Line QTY]),DISTINCT(FACT_Orders[Order Nr]))

[v-henryk-mstf · Accepted 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.

[stino123]

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. 

[Fowmy]

@stino123 

Sum if line quantity =
CALCULATE ( DISTINCTCOUNT ( FACT_Orders[Order Nr] ), FACT_Orders[Line QTY] > 2 )

[stino123]

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

[Fowmy]

@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])