Always use sum on different granularity

Hello,

I’ll try to describe the data and the reporting needs a bit more in detail so it hopefully becomes more clear what we are trying to achieve.

It’s about invoicing data in a medical sector, where a patient gets an invoice (InvoiceNumber) which can contain different items, with for example different medications (InvoiceLines), and for each of these InvoiceLines there is a Quantity (for example x amount of pills).

Now concerning the AmountType, for certain medications, a part gets covered by insurance, and a part has to be paid by the patient themselves (and depending on who pays, certain attributes are also different, hence the source file with data split on Invoice / InvoiceLine / AmountType level).

So for example one of the reports is to show split between the patient and insurance company Amounts, which also contains the Quantity in the overview. So based on the examples in my original post we get: “For InvoiceNumber 1, the patient paid 90 dollar (AmountType A), and the insurance company 10 dollar (AmountType B) for 1 unit of medication”.  

With the current setup, these kind of reports works without problems, but we also have other reporting needs on different levels.

For example, another report is an overview per invoice, where we want to see the total on “InvoiceNumber” level, so we would like to see “for InvoiceNumber 1, we have a total amount invoiced of 100 dollar for a total of 1 unit of medication”.

With our current setup however these reports don’t give a correct result, since we currently get “for InvoiceNumber 1, we have a total amount invoiced of 100, for a total of 2 units of medication”, since the quantity repeats on AmountType level, and gets added up.

So what we are trying to achieve, is some kind of smart summation, where we always show the “unique Quantity” on the “InvoiceNumber/InvoiceLine” level, and if we go on a lower level (InvoiceNumber/InvoiceLineNumber/AmountType) the “unique Quantity” gets repeated on each line, and if we go on a higher level (InvoiceNumber), the “unique Quantity” of the InvoiceLines under that InvoiceNumber gets added up.

Above examples are a bit simplified, but I hope it shows what we are trying to achieve, and hopefully this is possible with DAX (changing the source files is not an option since we don’t have those in control, but we could make some changes on datawarehouse level if really needed).