Always use sum on different granularity

[WGO]

Hello,

I was wondering if anyone could help with following model/dax problem:

Simplified, we are getting invoice data from 2 different sources, but the problem is that the granularity from the 2 sources is different, the one source is giving data on an Invoice / InvoiceLine level and contains the “Quantity” information, while the other source is giving data on an Invoice / InvoiceLine / AmountType level, and contains the “Amount” information.

This difference is because the payment for a certain product can for example be split in part for the customer, and part for the insurance, simplified it’s looking like this:

In our datawarehouse we combine these 2 sources, and currently we repeat the “Quantity” on each line if there is more than one AmountType:

We also have a tabular model on this datawarehouse, and depending on the level we are reporting on, we currently get wrong summations.

What we would like to achieve is that we always show the “unique” Quantity on InvoiceNumber / InvoiceLineNumber level, disregarding the actual reporting level

For example if we currently report on InvoiceNumber / InvoiceLineNumber / AmountType level, we see for each line the Quantity repeating, which is what we would like to keep:

But aside from that, if we report on InvoiceNumber / InvoiceLineNumber level, we currently see that the Quantity get summed up and “doubled”:

While we actually also would like to see the “unique” Quantity on this level, like this:

We assume this should be possible with DAX, we already tried a couple of things, but haven’t gotten the expected results.

Any help/advice on how to achieve this would be greatly appreciated!

[ray_codex · Accepted solution]

I would try this way:

VAR tbl =
    SUMMARIZE (
        [yourTable],
        [yourTable].[InvoiceNumber],
        [yourTable].[InvoiceLineNumber],
        "_quantity", AVERAGE ( [yourTable].[Quantity] )
    )
VAR result =
    SUMX ( tbl, [_quantity] )
RETURN
    result

[WGO]

We will need to do some more testing (especially to make sure it doesn't have a big performance impact), but so far this does exactly what we want to achieve!

Thank you and also AlexisOlson for your help/advice with this.

[AlexisOlson]

The odd thing to me is that you have repeated line numbers in Source 2. I get this is because you have multiple AmountType values, but maybe those should not be unpivoted. Can you explain what AmountType means in your case?

[WGO]

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).

[AlexisOlson]

Thanks for the explanation. I think it might make reporting easier in the long run if you pivot the amount type in your data warehouse so that you have columns

InvoiceNumber, InvoiceLineNumber, Quantity, Amount_TypeA, Amount_TypeB

If you don't want to go that route, then you need to be careful to always aggregate at the proper granularity similar to what @ray_codex suggested.

SumQuantity =
SUMX (
    SUMMARIZE (
        Data[InvoiceNumber],
        Data[InvoiceLineNumber],
        Data[Quantity]
    ),
    Data[Quantity]
)