How to calculate distinct sum or distinct count

Dashboard Designer SharePoint 2013/2016, Office 365 versions
1

Hi,
I have a list of invoices in which every sale can have multiple invoices, i want to display a chart of total sales based on the sales amount column vs total revenue based on the invoice amount column.

The problem is due to the one to many relationship, the sales amount is repeated many times as per the number invoices issued per sale. So how do i calculate the distinct sum of the sales amount column to get a correct report?

Thanks.

2

Hi,
You can add a column, say, “primary” invoice and mark with the flag only a single invoice per sale. Then request only primary invoices.

3

Thanks for the response,
Well, while your proposed solution technically solves the problem, it’s not quite user friendly, i mean those users who are making the data entry, they are many and they add invoices over quite long time for different contracts, they won’t be able to memorize or identify if it’s a primary one or not unless they have to check the list and filter it every time before they add a new invoice. So, would you recommend any better solution?

Thanks,