Forum Discussion
eliminate duplicate Shipping costs
I have a group of invoices with multiple line items. the shipping cost is at invoice level and is repeaded on each line. I want to find the total shipping cost for a group of in voices but am findind duplicates.
I need t write a measure so that the following table would calculte total shippingcost as $125.2
| InvoiceNo | ItemCode | ShippingCost |
| ABC | 780A | 25.2 |
| ABC | 800B | 25.2 |
| ADE | 9000C | 100 |
I am only coming up woth either 100 (the max shipping on all invoices) OR 150.4, the sum of all shippingcosts. the best measure I have so far is:
Hi ndev ,
I was able to reproduce this issue end to end. The duplication happens because the shipping cost is stored at the line item level, so using a normal SUM( ) ends up counting the same shipping value multiple times for a single invoice. To fix this, the calculation needs to first evaluate the shipping cost at the invoice level and then sum those invoice level values, which prevents the shipping cost from being double counted.
I used the below measure:Total Shipping =SUMX(VALUES( 'Shipping'[InvoiceNo] ),CALCULATE( MAX( 'Shipping'[ShippingCost] ) ))This works because VALUES(InvoiceNo) returns a distinct list of invoices, and MAX(ShippingCost) retrieves the single shipping value per invoice before summing. This eliminates duplicate counting while still respecting report filters.
Please find the below attached .pbix file for your reference.
Thank you.
12 Replies
- FBergamaschi
Super User
InvoiceNo ItemCode ShippingCost ABC 780A 25.2 ABC 800B 25.2 ADE 9000C 100 SUMX (
SUMMARIZE (
Table,
Table[InvoiceNo],
Table[ShippingCost]
),
Table[ShippingCost]
)If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
Murtaza_GhafoorPlease try this
1-Get one shipping value per invoice (preferably MAX)
2-Then sum those invoice-level values
Total Shipping :=
SUMX(
VALUES ( 'MyTable'[InvoiceNo] ),
CALCULATE ( MAX ( 'MyTable'[ShippingCost] ) )
)
It will create unique value for invoice and then add ups all the invoice value.
- cengizhanarslan
You want shipping counted once per invoice, then summed across invoices.
Use SUMX over the distinct invoice numbers and take a single value per invoice (MAX/MIN are fine because it’s repeated on every line):
Total Shipping = SUMX ( VALUES ( 'MyTable'[InvoiceNo] ), CALCULATE ( MAX ( 'MyTable'[ShippingCost] ) ) ) Hi ndev ,
I was able to reproduce this issue end to end. The duplication happens because the shipping cost is stored at the line item level, so using a normal SUM( ) ends up counting the same shipping value multiple times for a single invoice. To fix this, the calculation needs to first evaluate the shipping cost at the invoice level and then sum those invoice level values, which prevents the shipping cost from being double counted.
I used the below measure:Total Shipping =SUMX(VALUES( 'Shipping'[InvoiceNo] ),CALCULATE( MAX( 'Shipping'[ShippingCost] ) ))This works because VALUES(InvoiceNo) returns a distinct list of invoices, and MAX(ShippingCost) retrieves the single shipping value per invoice before summing. This eliminates duplicate counting while still respecting report filters.
Please find the below attached .pbix file for your reference.
Thank you.Hi ndev ,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support youHi ndev ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions
- AntrikshSharma
Community Champion
Try this:
Total Shipping Cost = CALCULATE ( SUMX ( VALUES ( Shipping[ShippingCost] ), Shipping[ShippingCost] ), ALLEXCEPT ( Shipping, Shipping[InvoiceNo] ) ) Hi,
Please try using the following measure.
Total Shipping :=
SUMX(
VALUES('MyTable'[Invoiceno]),
CALCULATE(SUM('MyTable'[ShippingCost]))
)- Olufemi7
Hi ndev,
Since ShippingCost is repeated on each line item, you need to sum it once per invoice.
Use this measure:Total Shipping := SUMX( VALUES('MyTable'[InvoiceNo]), -- distinct invoices in current filter context CALCULATE( MAX('MyTable'[ShippingCost]) ) -- one shipping amount per invoice )Result for your sample:
- ABC → 25.2
- ADE → 100
- Total → 125.2
Why it works:
VALUES('MyTable'[InvoiceNo]) produces a unique list of invoices under the current filters/slicers. For each invoice, MAX('MyTable'[ShippingCost]) (or MIN) returns the single invoice-level amount (it’s the same on all lines). SUMX then adds those per-invoice amounts, eliminating duplicates.If you have an Invoice Header table (one row per invoice), an even cleaner approach is:Total Shipping := SUM('InvoiceHeader'[ShippingCost])Hope this helps! - Praful_Potphode
Hi ndev ,
Try below measure:
Total shipping = var invoice_total=SUMMARIZECOLUMNS(MyTable[InvoiceNo],"MaxAmt",MAX(MyTable[ShippingCost])) return SUMX(invoice_total,[MaxAmt])If this doesnt work, please provide more information on input/output.
Please give kudos or mark it as solution once confirmed.
Thanks and regards,
Praful
- Ray_Minds
Solution Supplier
Please find the attached solution
