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.
Hi, Everyone
I have question please help.
I want to return the Total Amount from each Sale Order No. and want the value to be the same for all 3 Rows, which is based on the Account Code number = 60105001.
Expected results
Returns Total Amount = 72,900.00 for all 3 Rows.
How do I write the formula?
Solved! Go to Solution.
I suspect the issue here is that you have other sale order numbers that you don't want included in your formula. We just need another variable to handle that.
TotalAmount from AccCode = var AccountCode = "60105001"
var SaleOrder = [Sale Order No.]
var output = CALCULATE(
SUM('Reconcile Back Order'[Total Amount]),
ALL('Reconcile Back Order'),
'Reconcile Back Order'[Account Code] = AccountCode,
'Reconcile Back Order'[Sale Order No.] = SaleOrder
)
RETURN
output
Try the below. I didn't know your table's name, so i have called it "YourTable"
TotalAmount from AccCode = var AccountCode = [Account Code]
var output = CALCULATE(
SUM('YourTable'[Total Amount]),
ALL('YourTable'),
'YourTable'[Account Code] = AccountCode
)
RETURN
output
The value obtained is a sum of sums, still not what I want.
What I want is for all 3 Rows. to have the same value: 72,900.00, which is based on Account = 60105001 as shown in the picture above.
Can you help me again?
Write formula
Result formula
Oh thats an easy fix then. In the code sample I provided to you. Change the AccountCode variable to a static account code rather than the dynamic one.
From
var AccountCode = [Account Code]
to
var AccountCode = "60105001"
I have made the correction. The results obtained It's still not what you want.
But what I got is It's the same result for all 3 rows. I think I'm close to getting the correct answer. If the answer is 72,900.00 then it is correct.
Write formula
Result formula
I suspect the issue here is that you have other sale order numbers that you don't want included in your formula. We just need another variable to handle that.
TotalAmount from AccCode = var AccountCode = "60105001"
var SaleOrder = [Sale Order No.]
var output = CALCULATE(
SUM('Reconcile Back Order'[Total Amount]),
ALL('Reconcile Back Order'),
'Reconcile Back Order'[Account Code] = AccountCode,
'Reconcile Back Order'[Sale Order No.] = SaleOrder
)
RETURN
output
The results were as expected.
Write formula
Result formula
Very Thankful.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
107 | |
88 | |
76 | |
67 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |