Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
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.
Yeah, I was Looking for a measure to achieve this Subtraction post grouping
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:
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
22 | |
7 | |
6 | |
6 | |
6 |
User | Count |
---|---|
27 | |
10 | |
10 | |
9 | |
6 |