Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
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.