Sum Distinct Invoice Numbers

arielrodrigues — Mon, 02 Aug 2021 20:39:57 GMT

Hey guys.

I have a sheet (attached) that brings me the invoices, quantity sold of the item, and the barcode of them.

In that sheet, it brings me repeatedly the total of items sold in that invoice and if I sum it, it goes wrong.

There are different invoices with the same count of items sold. The "DISTINCT" takes it out of the sum.

In the below table, "Qtde. Faturada" refers to SUM(DISTINCT IF(NUMNFV,QTDFAT)).

The total row at the top shows 2.950, but it needs to show 5.170

I only get the satisfying result if I change the way this, from "Total of Expression" to "Sum rows"

To make a chart after that, it's the same problem. Does anyone have an answer to that?

Re: Sum Distinct Invoice Numbers

Vegar — Tue, 03 Aug 2021 06:50:58 GMT

Maybe an aggregated aggregation is what you are trying to to do. Take a look at this example.

The expression I've used is: SUM(aggr(SUM(distinct QUANTITY),INVOICE,[PRODUCT CODE]))

I where not you able to reproduce the figures 5.170 your Sheet.xlsx as a data source? If my suggestion above does not solve your issue then please provide a load script that reads your excel in a correct manner. For my solution I did this data model.

topic Re: Sum Distinct Invoice Numbers in App Development

Sum Distinct Invoice Numbers

Re: Sum Distinct Invoice Numbers