Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Solved! Go to Solution.
@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])
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.
@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])
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.
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
108 | |
108 | |
93 | |
61 |
User | Count |
---|---|
169 | |
138 | |
135 | |
102 | |
86 |