Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
bi_analytics
Frequent Visitor

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.

bi_analytics_1-1649979967929.png

 

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. 

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@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 YouTube.gif 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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

2 REPLIES 2
parry2k
Super User
Super User

@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 YouTube.gif 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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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. 

 

bi_analytics_0-1650025030731.jpeg

 

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors