Summarize and Filter Data in PBI

Anonymous — Mon, 09 Jan 2023 16:47:31 GMT

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

Re: Summarize and Filter Data in PBI

adudani — Tue, 10 Jan 2023 00:11:34 GMT

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.

Re: Summarize and Filter Data in PBI

Anonymous — Tue, 10 Jan 2023 02:01:55 GMT

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

Re: Summarize and Filter Data in PBI

FreemanZ — Fri, 13 Jan 2023 05:47:59 GMT

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.

topic Re: Summarize and Filter Data in PBI in DAX Commands and Tips

Summarize and Filter Data in PBI

Re: Summarize and Filter Data in PBI

Re: Summarize and Filter Data in PBI

Re: Summarize and Filter Data in PBI