Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi Guys,
I am fairly new to Power BI so I apologise if this seems too easy.
Basically I have a table that pulls all my sales invoice data through. My problem is that the invoice number and payment figure appear multiple times, one time for each line item on the orginal document. I'd like to summarise the GP figure to the invoice number and keep only one instance of the payment made all in a seprate table. Here is a basic version of what I mean below.
Document Item GP Payment Made Date
INVOICE 1 ITEM 1 $5 $300 01/01/18
INVOICE 1 ITEM 2 $100 $300
INVOICE 1 ITEM 3 $25 $300
INVOICE 2 ITEM 1 $10 $275 02/02/18
INVOICE 2 ITEM 2 $15 $275
INVOICE 2 ITEM 3 $150 $275
This is what I am trying to achieve, a seperate table that shows
Document SumGP Payment Date
INVOICE 1 $130.00 $300 01/01/18
INVOICE 2 $175.00 $275 02/01/18
There are many more columns in the first table with data relevant for other areas of reporting but this is all that it is required in the second table.
Thanks in advance
Solved! Go to Solution.
Hi,
You can do this in the Query Editor and then Load this data into the Data Model. Here is the M code
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Document", type text}, {"Item", type text}, {"GP", Int64.Type}, {"Payment Made", Int64.Type}, {"Date", type datetime}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Item"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Document"}, {{"Gross Profit", each List.Sum([GP]), type number}, {"Payments", each List.Min([Payment Made]), type number}, {"Dates", each List.Min([Date]), type datetime}})
in
#"Grouped Rows"
You may download the workbook from here. The very same can be done directly in PowerBI desktop as well.
@Ashish_Mathuris 100% correct and this is probably the best way to achieve what you are looking for.
if you want a quick and dirty DAX solution you could create two measures:
SumGP = SUM(Table1[GP])
Payment = AVERAGE(Table1[Payment Made])
then drag them into your report tables.. it would aggregate the GP correctly at invoice level or item level as you require but keep payment as the average of all payment entries for that invoice no... (note it would average the payment for all invoices if you rolled it up which might not be ideal)
you could probably also build a summary table with SUMMARIZE if you needed to do this in DAX instead of Query editor.
Hi,
You can do this in the Query Editor and then Load this data into the Data Model. Here is the M code
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Document", type text}, {"Item", type text}, {"GP", Int64.Type}, {"Payment Made", Int64.Type}, {"Date", type datetime}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Item"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Document"}, {{"Gross Profit", each List.Sum([GP]), type number}, {"Payments", each List.Min([Payment Made]), type number}, {"Dates", each List.Min([Date]), type datetime}})
in
#"Grouped Rows"
You may download the workbook from here. The very same can be done directly in PowerBI desktop as well.
Wow thanks alot!
You are welcome.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
87 | |
84 | |
66 | |
49 |
User | Count |
---|---|
140 | |
114 | |
110 | |
59 | |
59 |