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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
fbarry
Frequent Visitor

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.

fbarry_0-1595771776792.png

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...

fbarry_1-1595772128557.png

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 

1 ACCEPTED 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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

10 REPLIES 10
mahoneypat
Employee
Employee

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.
receipts.png
 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI 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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI 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. 

 

fbarry_0-1595955091393.png

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI 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! 

Greg_Deckler
Super User
Super User

@fbarry - Try changing your measure to this:

 

SumX Measure = SUM(Invoice_items[Amount]) * [Inv % Paid]

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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 

[Inv % Paid] measure. It's just returning blank results when this measure is used in the expression. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors