Sum of rows with Unique ID

[PaulPed]

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

[v-jiascu-msft]

@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

[PaulPed]

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

[v-jiascu-msft]

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

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

Best Regards!

Dale

[AlbertoFerrari]

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

[Greg_Deckler]

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.