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
Anonymous
Not applicable

Summarize and Filter Data in PBI

Hi PBI Experts,

I am looking to show only rows where ReceiptAmount and InvoiceAmount are compared for the same Receipt and if Data become 0, we need to eliminate that row.
Example here: Group at ReceiptKey, For same Receipt Key do ReceiptAmount - InvoiceAmount , But Issue is ReceiptAmount and Invoice Amount appear in different row

Here is the Sample format of Data, Expecting a Measure which gives ReceiptAmountMinusInvoiceAmt.
Example for Receiptkey =102, Total Receipt Amount =(20+20)=40 and Invoice Amount =40 , so Receipt Amont Minus Invoice Amount =Blank to be shown against ReceiptKey 102 
Sample Data for Reference


sheikhbiuser_0-1673281557967.png

 

3 REPLIES 3
FreemanZ
Super User
Super User

hi @Anonymous 


If you plot a table visual the Receipt Key column and a measure like this:
Measure = SUM(TableName[Receipt Amount]) - SUM(TableName[Invoice Amount])
It shall get what you want. Or?

 

If there is issue, please consider
1) paste your sample as text here
2) provide your expected result
3) @someone, if you seek further suggestions.

Anonymous
Not applicable

Yeah, I was Looking for a measure to achieve this Subtraction post grouping

adudani
Super User
Super User

hi @Anonymous

 

my solution in power query using:

1. Receipt ( query1) 
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hc8xDoAgDAXQu3SGpBTQM+joahi8AKPnl4JEWgcSUhry8lvOExw6MHBwQWf3K1vivhyH2BpIpjultnxL3CRxIhckkUgy8GN+xlRWmfu+hmGs5zwuGNRH2h1/Lk4dq6WqukcZ7Aes5SpW9D2uwvQA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Receipt Key" = _t, Receipt = _t, #"Transaction Date" = _t, Invoice = _t, #"Receipt Amount" = _t, InvoiceAmount = _t, #"Expected Output" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Receipt Key", Int64.Type}, {"Receipt", type text}, {"Transaction Date", type date}, {"Invoice", type text}, {"Receipt Amount", Int64.Type}, {"InvoiceAmount", Int64.Type}, {"Expected Output", Int64.Type}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Transaction Date", "Receipt", "Receipt Key", "Receipt Amount"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([Receipt Amount] <> null)),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"Receipt Key", "Receipt"}, {{"Receipt Amount", each List.Sum([Receipt Amount]), type nullable number}})
in
#"Grouped Rows"


2. Output ( Query2):

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hc8xDoAgDAXQu3SGpBTQM+joahi8AKPnl4JEWgcSUhry8lvOExw6MHBwQWf3K1vivhyH2BpIpjultnxL3CRxIhckkUgy8GN+xlRWmfu+hmGs5zwuGNRH2h1/Lk4dq6WqukcZ7Aes5SpW9D2uwvQA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Receipt Key" = _t, Receipt = _t, #"Transaction Date" = _t, Invoice = _t, #"Receipt Amount" = _t, InvoiceAmount = _t, #"Expected Output" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Receipt Key", Int64.Type}, {"Receipt", type text}, {"Transaction Date", type date}, {"Invoice", type text}, {"Receipt Amount", Int64.Type}, {"InvoiceAmount", Int64.Type}, {"Expected Output", Int64.Type}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Receipt Key", "Invoice", "InvoiceAmount"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([InvoiceAmount] <> null)),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"Receipt Key", "Invoice"}, {{"Invoice Amount", each List.Sum([InvoiceAmount]), type nullable number}}),
#"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"Receipt Key"}, Receipt, {"Receipt Key"}, "Receipt", JoinKind.LeftOuter),
#"Expanded Receipt" = Table.ExpandTableColumn(#"Merged Queries", "Receipt", {"Receipt", "Receipt Amount"}, {"Receipt.1", "Receipt Amount"}),
ReceiptMinusInvoice = Table.AddColumn(#"Expanded Receipt", "ReceiptMinusInvoice", each [Receipt Amount]-[Invoice Amount] ),
#"Changed Type1" = Table.TransformColumnTypes(ReceiptMinusInvoice,{{"ReceiptMinusInvoice", type number}})
in
#"Changed Type1"

 

 

__________________________

Was trying in DAX:
 the following code leads to the tables required: however, still figuring out the subtraction piece:

Receipt table =

var Receipts =
FILTER(
SUMMARIZE(
'Receipt (2)',
Receipt[Receipt Key], 'Receipt (2)'[Receipt],
"Receipt Amount", sum('Receipt (2)'[Receipt Amount])
), 'Receipt (2)'[Receipt] <> blank()
)

var _Invoices=
FILTER(
SUMMARIZE(
'Receipt (2)',
Receipt[Receipt Key], 'Receipt (2)'[Invoice],
"Invoice Amount", sum('Receipt (2)'[InvoiceAmount])
),
'Receipt (2)'[Invoice] <> Blank()
)


return _Invoices


If i figure the subtraction, I will reply on the thread.
 
Appreciate a thumbs up if this is helpful.
 
Please accept as solution if the power query solution is acceptable.
Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash

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.