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 illustr...
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.

parry2k's avatar
parry2k
Super 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.