Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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..
https://1drv.ms/u/s!AvRI47UNz2eugZc2or6rNYOqgV2grQ?e=MZ0xBc
Solved! Go to Solution.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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).
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi @mahoneypat,
If I select a Product (product B in the below example), the rows on the table below will not tot up the the total value. It is actually the rows that are correct but the total is wrong. It is the same total as appearing on the Product B row in the upper table, but this is also wrong.
The reason for this (I believe) is that I'm using the [Inv % Paid] measure within my new measure, which calculates at invoice level and not invoice_items level and therefore if I just use a simple SUM it will not be an iterative calculation across each invoice when totalling.
Total of SumX Measure should be 5,283.20 as that is the sum of the rows.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
😁YES! This has worked a treat for me. I'd be scared to tot up all the hours I spent on this!
Thanks you @mahoneypat, much appreciated
If only I could hit the kudos button more than once!
@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
Hi @Greg_Deckler ,
I've had a look at your Final Word post (which is a great article btw). However, the Summarize function does not appear to like my
User | Count |
---|---|
40 | |
26 | |
22 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |