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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. 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
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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.