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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
garretc
New Member

SUMIF Help within one table

Hi all,

 

Hope you can help me. I have a dataset which contains thousands of rows of invoice data. It may contain more than one row per invoice (because it's across multiple cost centres). I am analysing invoice volumes by Supplier and Supplier Categories. I would like to be able to filter so that I can show only those invoices with a total net value of less than, say, $25. The purpose is to analyse where the in-house processing cost might be higher than the actual goods/services being procured. 

 

I am using Tree Graphs with [Supplier Category] as the Category, [Supplier] as the details and Distinct Count of [Invoice ID] as the value. However, I believe that when I set a slicer on the dashboard against [Net Amount] and set it to show all values <$25, it is removing only suppliers/supplier categories where the total is >$25, not at the unique invoice ID level.

 

So I'm thinking that I need to create a column that flags whether an invoice total (not invoice line) is less than $25. And to do that, I need to calculate a cumulative total based on the invoice ID. If I were in Excel, I'd simply do SUMIF([Invoice ID],[@[Invoice ID]],[Net Amount]) but I can't seem to figure out an equivalent here.

 

I guess I could create a separate table out of the data, and point the dashboards to here, but it feels a bit inelegant.

 

 

Invoice IDSupplierSupplier CategoryCost CentreNet Amount
1ACMEProductsABC $                15.00
1ACMEProductsDEF $                16.00
1ACMEProductsGHI $                17.00
2Rollercoaster ManufacturingProductsABC $             100.00
3Boomtown IndustriesProductsDEF $                  2.00
4Distributed GoodsTransportGHI $                50.00
5Import Export LimitedProductsABC $       10,000.00
6Eagle IncTransportABC $                  5.00

 

And below is the dataset with what I desire to see:

 

Invoice IDSupplierSupplier CategoryCost CentreNet AmountTotal Invoice Amt<$25?
1ACMEProductsABC $                15.00 $                 48.00FALSE
1ACMEProductsDEF $                16.00 $                 48.00FALSE
1ACMEProductsGHI $                17.00 $                 48.00FALSE
2Rollercoaster ManufacturingProductsABC $             100.00 $               100.00FALSE
3Boomtown IndustriesProductsDEF $                  2.00 $                   2.00TRUE
4Distributed GoodsTransportGHI $                50.00 $                 50.00FALSE
5Import Export LimitedProductsABC $       10,000.00 $         10,000.00FALSE
6Eagle IncTransportABC $                  5.00 $                   5.00TRUE

 

1 ACCEPTED SOLUTION
Greg_Deckler
Community Champion
Community Champion

@garretc Try these:

Total Invoice Amt Column = 
    VAR __InvoiceID = [Invoice ID]
    VAR __Table = FILTER('Table', [Invoice ID] = __InvoiceID)
    vAR __Result = SUMX(__Table, [Net Amount])
RETURN
    __Result

< $25 Column = IF([Total Invoice Amt] > 50, TRUE(), FALSE())


Follow on LinkedIn
@ 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!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
Greg_Deckler
Community Champion
Community Champion

@garretc Try these:

Total Invoice Amt Column = 
    VAR __InvoiceID = [Invoice ID]
    VAR __Table = FILTER('Table', [Invoice ID] = __InvoiceID)
    vAR __Result = SUMX(__Table, [Net Amount])
RETURN
    __Result

< $25 Column = IF([Total Invoice Amt] > 50, TRUE(), FALSE())


Follow on LinkedIn
@ 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!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler Thanks so much for taking the time to look into this and reply - this worked a charm!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.