Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
DaveBFT
Frequent Visitor

Sum of Gross Proft

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

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

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.

 

Untitled.png

 

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

4 REPLIES 4
dearwatson
Continued Contributor
Continued Contributor

@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
Super User
Super User

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.

 

Untitled.png

 

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Wow thanks alot!

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.