Forum Discussion
SUMX issue
Hi All,
I'm having an issue trying to get a SUMX expression to return the expected results, and after many hours of toil I am now officially stumped.
Ultimately, am trying to create a measure to calculate the amount paid on an invoice_item. In order to do this I have to calculate the percentage of the Invoice paid and multiple this % by the invoice_item amount to give me the paid amount at line level.
Below is an example of my model, I've also attached sample data.
My SUMX measure is as follows...
SumX Measure = SUMX(Invoice_items,Invoice_items[Amount] * [Inv % Paid])'Inv % Paid' measure is...
Inv % Paid = CALCULATE(DIVIDE([Inv_Receipts] , SUM(Invoices[Amount])),CROSSFILTER(Invoice_items[Inv_No],Invoices[Inv_No],Both))and is returning mainly blank results...
I'm clearing doing something wrong but cannot see the trees from the forest at this stage. Ideally, I wouldn't even be using a SUMX expression as it won't be efficient in my live dataset, but because I'm multiplying by a % I think I need the row context to ensure the total's are correct. I can't use a calculated column as I need it to be dynamic, based on filter changes (dates).
I would really appreciate a steer in the right direction,
Thanks.
pbix file below..
Please try this one. It gets the same results on rows and correct total. This is the more logical way I should have written it in the first place.
New SUMX =
SUMX (
SUMMARIZE ( Invoice_items, Invoices[Inv_No], Invoice_items[Product] ),
CALCULATE (
DIVIDE ( SUM ( Receipts[Amount] ), SUM ( Invoices[Amount] ) )
* SUM ( Invoice_items[Amount] )
)
)If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
10 Replies
I looked at your pbix. Things get simpler if you use the Inv_No from your Invoices table in your table visual (instead of the one from the Inv_Items table). If so, you just adapt the two measure below to get your desired result (I believe).
Inv % Paid = DIVIDE([Inv_Receipts], SUM(Invoices[Amount]))SumX Measure = SUM(Invoice_items[Amount]) * [Inv % Paid]Also, apply a Filter on this visual where Inv_Items[Amount] is not blank to get this result.If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Hi mahoneypat ,
Thanks for your reply.
Your suggestion works for unfiltered data, but as soon as I apply a filter to this visualisation (filter by Product) the totals are incorrect. I need to be able to filter this by Product.
It seems to work when I filter by Product. If you can further describe and send screen shot of what you are seeing, I can adapt it.
Regards,
Pat
fbarry - Try changing your measure to this:
SumX Measure = SUM(Invoice_items[Amount]) * [Inv % Paid]Hi Greg_Deckler ,
Thanks for your reply.
Although your suggested mesure will work at row level, the total will not tot up correctly as it's not an iterated expression.
fbarry - For the measure total issue, use this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
