cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
PaulPed Frequent Visitor
Frequent Visitor

Sum of rows with Unique ID

Hi there,

 

I have a data set which portrays line item detail of sales by invoice. There are multiple rows to each invoice (Unique ID) for the varying items sold. I want to SUM all of the rows pertaining to each invoice in order to ascertain the average sale per invoice. I also still need the data to remain in the rowed format in order to support existing dashboards.

 

Thanks,

Paul

5 REPLIES 5
Super User
Super User

Re: Sum of rows with Unique ID

You can either put your Invoice ID and the sum of the value in a table visualization or use SUMMARIZE and group by your Invoice ID.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Microsoft v-jiascu-msft
Microsoft

Re: Sum of rows with Unique ID

@PaulPed,

 

Hi Paul,

 

For the average, you could try this formula as a measure and put it in a Card visual.

Average Sales per Invoice =
DIVIDE ( SUM ( 'sales'[amount] ), DISTINCTCOUNT ( 'sales'[ID] ), 0 )

No changes are applied to the data set. 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
PaulPed Frequent Visitor
Frequent Visitor

Re: Sum of rows with Unique ID

Hi Dale,

 

I’m not looking for the average.

 

I have a data export of all my sales data which is by line item and there are multiple rows associated with the same invoice number. Below is an example:

 

Sales Item Id

Sales Qty

Unit Price

Invoice Id

Invoice Date

Month

Sales Amt

Sales Invoice Total

CTG970B

1.00

57.62

SCI034545

7/6/16

Jul-16

$            57.62

232.59

CTG971C

1.00

54.42

SCI034545

7/6/16

Jul-16

$            54.42

232.59

CTG971M

1.00

54.42

SCI034545

7/6/16

Jul-16

$            54.42

232.59

CTG971Y

1.00

54.42

SCI034545

7/6/16

Jul-16

$            54.42

232.59

 

As you can see I have a Sales Invoice Total column which is summing up the line items as part of the ERP, however, I can’t use this to ascertain a true average per invoice. I either want to isolate a single Total per Unique Invoice ID or alternatively SUM up the Sales Amt column of each line item to portray a total for that invoice.

 

Thanks,

Paul

Re: Sum of rows with Unique ID

Something like this might work:

Avg Invoice = 
AVERAGEX ( 
    ADDCOLUMNS ( 
        SUMMARIZE ( 
            Sales,
            Sales[Invoice ID]
        ),
        "Amt", CALCULATE ( SUM ( Sales[Sales Amt] ) )
    ),
    [Amt]
)

With that said, it would be better (and faster) if you create an Invoice table with the precomputed total and create the proper set of relationships. Having the total denormalized in the line detail table is a bad practice because many attributes (date, month and so on) are pertinent to the invoice, not to the single date and the same calculation, with an invoice table, would be a much simpler AVERAGE.

 

 

I usually teach that when DAX becomes complex, it is because the model need to be optimized... on a well designed model the DAX code is nearly always very simple (Ok, enough for my pedantic moment :))

 

Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com

Alberto Ferrari - SQLBI
Microsoft v-jiascu-msft
Microsoft

Re: Sum of rows with Unique ID

@AlbertoFerrari,

 

Hi Alberto,

 

Thanks for the best practice. It will be very helpful.

 

@PaulPed

 

Hi Paul,

 

I wonder if you want a true total of each invoice. If so, you can try this formula.

TotalOfEachInvoice =
SUM ( Sales[Sales Amt] )

Or, you can try [Avg Invoice] from @AlbertoFerrariSum of rows with Unique ID .jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

If you get the right answer, please mark it as solution.

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)