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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Responsive Resident
Responsive Resident

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

 

 

"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte
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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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