Forum Discussion

stino123's avatar
stino123
Helper II
4 years ago
Solved

Sumifs in power bi

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

 

 

DAX Calculation
  • v-henryk-mstf's avatar
    v-henryk-mstf
    4 years ago

    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.

5 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    4 years ago

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

  • stino123's avatar
    stino123
    Helper II
    4 years ago

    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's avatar
      Fowmy
      Super User
      4 years ago

      stino123 

       

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

       

      • stino123's avatar
        stino123
        Helper II
        4 years ago

        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

         

         

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    4 years ago

    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.