Forum Discussion

bi_analytics's avatar
bi_analytics
Frequent Visitor
4 years ago
Solved

SUMX Total Different from Displayed Row Values

Dear Power BI friends.

I have a scenario where I can't figure out why SUMX gives me a different total than the sum of the rows displayed in the table.

 

I have made a simplified example to illustrate my issue:

The dataset contains rows of order data where each customer has a unique ID and an order amount.

To illustrate my issue, I created a measure called "Amount per Customer" the code is pasted below.
I also made a table using the Customer IDs and Amount columns from the dataset, which you can see to the right of the picture below. I added this to control my "Amount per Customer" measure.

 

As you can see in the picture, the "Amount per Customer" measure displays the correct values for each row. However, the total is way off. The correct calculation is 926, while my measure shows 213,906.

 

I created another measure, "Amount per Customer 2", where I do a SUMX of the first measure. This measure seems to work, but I'm not sure why. The code is posted below.

 

Amount per Customer = 

VAR _Table =
ADDCOLUMNS(
    DISTINCT(Orders[Customer ID]),
    "Amount",
    SUM(Orders[Amount])
)

RETURN
SUMX(_Table, [Amount])

 

Amount per Customer 2 = 
SUMX(
    VALUES(Orders[Customer ID]), 
    [Amount per Customer]
    )

 

Ideally, I would like only one measure with the total summing up correctly. Is there any way to modify my "Amount per Customer" measure to give me the correct result?

 

Do you know what causes the incorrect total from my "Amount per Customer" measure? And why is my "Amount per Customer 2" measure working?

 

I appreciate any help you can provide. 

Need Help
  • parry2k's avatar
    parry2k
    4 years ago

    bi_analytics this is what you need to fix, to understand why it giving the funny result, stay tuned for my upcoming video based on this post:

     

    Amount per Customer = 

VAR _Table =
ADDCOLUMNS(
    DISTINCT(Orders[Customer ID]),
    "Amount",
    CALCULATE(SUM(Orders[Amount]))
)

RETURN
SUMX(_Table, [Amount])

     

     

     

    ✨ Follow us on LinkedIn and  to our YouTube channel

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

2 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    4 years ago

    bi_analytics this is what you need to fix, to understand why it giving the funny result, stay tuned for my upcoming video based on this post:

     

    Amount per Customer = 

VAR _Table =
ADDCOLUMNS(
    DISTINCT(Orders[Customer ID]),
    "Amount",
    CALCULATE(SUM(Orders[Amount]))
)

RETURN
SUMX(_Table, [Amount])

     

     

     

    ✨ Follow us on LinkedIn and  to our YouTube channel

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

    • bi_analytics's avatar
      bi_analytics
      Frequent Visitor
      4 years ago

      Thank you very much, parry2k   This solved my issue.

       

      I'm wondering if you can help me with the following problem. This is similar to my example above.
      Unfortunately, I cannot share any screenshots because of confidentiality, but I can share the code I used for the measure.

       

      My table contains these columns "Order ID," "Customer ID," and "Delivery Date." I want to determine whether or not a customer has placed an order after the delivery date of the order ID displayed.

       

      I want to display a 1 if it's TRUE and 0 if FALSE. I'm able to get the correct 1's and 0's in the rows of the table using the measure pasted below. However, the total is incorrect, as shown in green in the example table below. 

       

       

      Order Booked After Delivery = 

VAR _Current_Date = SELECTEDVALUE(Orders[Delivery Date])

VAR _Table =
ADDCOLUMNS(
    DISTINCT(Orders[Customer ID]),
    "Latest Order Date",
        CALCULATE(
        MAX(Orders[Order Date]), 
        ALLEXCEPT(Orders, Orders[Customer ID]),
        ),
    "Current Date",
    _Current_Date
)

VAR _Add_Control_Column =
ADDCOLUMNS(
    _Table,
    "Control",
    IF([Latest Order Date] >= [Current Date] && [Latest Order Date] <> 0 && [Current Date] <> 0, 1, 0)
)

VAR _Sum = SUMX(_Add_Control_Column, IF([Control] > 0, [Control], 0))

RETURN
_Sum

       

      As with my previous issue, I'm able to get the correct total by adding another measure referring to my first measure. However, this is not ideal. Here is the code for the extra measure:

       

      Order Booked After Delivery SUMX = 
SUMX(VALUES(Orders[Customer ID]), [Order Booked After Delivery])

       

       

      Is there any way for me to modify my first measure to display the correct total? I tried to add a CALCULATE function without any luck.

       

      Thank you in advance.