QuickBooks Connection Duplicate Invoice Records

[Black783]

When a QuicBooks Online Invoice has Sales Tax and Shipping values assoicated with it, there are 3 records assoicated with that invoice on the Invoice table.  The line item amount and Sales tax amount show up 3 times.  See screen shot.  What is the best way to combine all 3 records so that I get the correct line item amount and sales tax.  Meaning the line item amount is $200, I need the sum to be $200 and not $600.  The sales tax to be $15.50 and not triple the amount.

[v-ljerr-msft]

Hi @Black783,

According to your description, you should be able to use MAX Function (DAX) to create two measures to get correct line item amount and sales tax, then show them on the report in this scenario. The formulas below are for your reference.

Line Amount = MAX ( 'Your table name'[Line.Amount] )

Total Tax = MAX ( 'Your table name'[TxnTaxDetail.Totaltax] )

Regards

[Black783]

Appreciate the assistance but this approach would require an excessive amount of measures.  An invoice can have many line items with references and assoicated amounts so you have to develop a measure for each one plus sales tax, freight, etc. 

Also I'm not sure you can write an IF statement to get the proper results.

It seems to me that the data set from the QuickBooks connection is flawed.  If I was extracting from SQL Server, I would have individual records for each line item, freight, and sales tax. 

[v-ljerr-msft]

Hi @Black783,

---

It seems to me that the data set from the QuickBooks connection is flawed.  If I was extracting from SQL Server, I would have individual records for each line item, freight, and sales tax. 

---

QuickBooks Online connector is still a preview connector, and is still under development currently.

Could you submit your requirement on Power BI Ideas and vote it up to improve Power BI and make it better?

Regards

[Black783]

I will submit.