SUMX with Lookup

Hey Axel_hnk ,

sure, you can use variables within the SUMX function and filter the percentage table accordingly.

The following measure should produce the result you want:

SalesAmount corrected =
SUMX (
    Table_Sales,
    VAR vProductCurrentRow = Table_Sales[ProductId]
    VAR vDatekeySoMCurrentRow = Table_Sales[DatekeySoM]
    VAR vProductIdDate = Table_Sales[ProductIdDate]
    RETURN
        Table_Sales[SalesAmount]
            * CALCULATE (
                MAX ( Table_Percentage[Percentage] ),
                Table_Percentage[ProductID] = vProductCurrentRow,
                Table_Percentage[DatekeySoM] = vDatekeySoMCurrentRow,
                Table_Sales[ProductIdDate] = vProductIdDate
            )
)

And here is the result:

And with datatype B:

Please find my example file attached.

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic