Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Is there a way to do set analysis sum with defining a parameter as distinct?
I have data from invoice header and invoice lines. When summed up, they equal each other. But when I sum the overall
Sum(inv_ln_total) ..... and.... Sum(inv_gross_total), it equals the picture below. I think it is duplicating data somewhere or there are duplicate invoices in the report I pulled.
What I want to do is something like this:
--> sum({<Distinct Invoice ID>} Invoice Header Total)
Is this possible to set up an ID that is distinct within set analysis
This sounds like a problem with the data model, so I'd suggest trying to get that looked at and possibly fixed if possible. Perhaps you are joining the lines and header into a single table, resulting in the duplication of header values?
You might be able to work around it by using aggr and Only(), something along the lines of:
sum(aggr(only([Invoice Header Total]),[Invoice ID]))
This sounds like a problem with the data model, so I'd suggest trying to get that looked at and possibly fixed if possible. Perhaps you are joining the lines and header into a single table, resulting in the duplication of header values?
You might be able to work around it by using aggr and Only(), something along the lines of:
sum(aggr(only([Invoice Header Total]),[Invoice ID]))
Thanks for the reply!
that formula work as well as one I found from a coworker. Putting this here to help someone who comes along to find this answer!
Sum(Aggr(Sum(invoice header total), inv_id))