Grand total in Matrix comparing filtered greater values from different rows

[ITManuel]

Hi,

I have an issue with the grand total in a matrix. 

1st an example for which my solution works:

Data1:

I would like to compare "Order value" with "Invoice value" and return whatever is greater, then show the sum per Supplier and the Grand total.

I'm using the following measure which works for this type of data:

Coststobeconsidered1.1 =
VAR OrderValue =
    SUM ( Data1[Order value] )
VAR InvoiceValue =
    SUM ( Data1[Invoice value] )
VAR Coststobeconsidered =
    IF ( OrderValue > InvoiceValue, OrderValue, InvoiceValue )
RETURN
    IF (
        HASONEFILTER ( Data1[Supplier] ),
        Coststobeconsidered,
        SUMX (
            Data1,
            IF (
                Data1[Order value] > Data1[Invoice value],
                Data1[Order value],
                Data1[Invoice value]
            )
        )
    )

 Or simply:

Coststobeconsidered1.2 =
SUMX (
    Data1,
    IF (
        Data1[Order value] > Data1[Invoice value],
        Data1[Order value],
        Data1[Invoice value]
    )
)

Result1:

The issue I have is, that my real data looks like this with value for "Order value" and "Invoice value" in different rows

Data2:

Using now the same dax as mentioned above is resulting understandably in the following:

For the first 1.1 code the values filtered by supplier are matching, the grand total not. For the 2.2 code nothing is matching. I understand the issue, it compares values against blank values, consequently sums up both columns, but so far I do not got to the solution.

How can I solve the issue, if possible by using measures only and not calculated columns or tables.

Any help appreciated.

The pbix file under the following link.

https://we.tl/t-6QW53gldOX

Thank you very much and best regards

[ITManuel]

Hi @v-robertq-msft ,

works great. 

Thank you very much

[v-robertq-msft · Accepted solution]

Hi, @ITManuel 

According to your description and picture, I think you want to make the total row of measure [Coststobeconsidered] display the correct total: 108 in the real data table ‘data2’, am I right?

You can try this measure:

Coststobeconsidered2.3 =

var _data=

SUMMARIZE('Data2',Data2[Supplier],"Invoice",SUM(Data2[Invoice value]),"Order",SUM(Data2[Order value]))

return

SUMX(_data,

IF([Invoice]>=[Order],[Invoice],[Order]))

Then place the measure into the table chart ‘data2’, I think you can get what you want, like this:

You can download my test pbix file here

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.