Sum of Gross Proft

[DaveBFT]

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

[dearwatson]

@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.

[Ashish_Mathur · Accepted 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.

[DaveBFT]

Wow thanks alot!

[Ashish_Mathur]

You are welcome.