SUMX with Lookup

[Axel_hnk]

Hi there,
I need to calculate the following:
I have two tables (no relationship available): 

[Table_Percentage] - Not all ProductId available as Reference

AND

[Table_Sales]

ProductIdDate is a concatenation of ProductId and DatekeySoM (SoM = StartOfMonth)

What I want to achieve

Selection Datatype A:

Selection Datatype B:

Question

I want to calculate the SalesAmount% on the ProductIdDate level and need to apply a percentage from the "Table_Percentage" where both ProductIdDate levels match. If not null will be the result.

On the aggregation level I need a strict sum of alle items calculated at the ProductIdDate level.

The inital calculation can be something like this:

SUMX(
    VALUES('Table_Sales'[ProductIdDate]),
    SUM('Table_Sales'[SalesAmount]) *
    [LookUpValue of 'Table_Percentage']
)

This is what I am looking for:
[LookUpValue of 'Table_Percentage']

Can anybody help?
Thx in advance

[v-luwang-msft]

Hi @Axel_hnk ，

Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.

Best Regards
Lucien

[selimovd · Accepted solution]

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

[Axel_hnk]

Hi Denis,

thanks for your solution.

In order to make it work in my case I needed to tweak the code a little:

SalesAmount corrected =
SUMX (
Table_Sales,
VAR vProductCurrentRow = Table_Sales[ProductId]
VAR vDatekeySoMCurrentRow = Table_Sales[DatekeySoM]

RETURN
Table_Sales[SalesAmount]
* CALCULATE (
    MAX ( Table_Percentage[Percentage] ),
        Filter(
            Table_Percentage,
            Table_Percentage[ProductID] = vProductCurrentRow &&
            Table_Percentage[DatekeySoM] = vDatekeySoMCurrentRow
        )
   )
)

[Signore_Ands]

@Axel_hnk - what if you add a column to [Table_Sales] to lookup the % from the other table?

Something like LOOKUPVALUE('Table_Percentage','Table_Percentage'[PorductIDDate],'Table_Sales'[PorductIDDate],"0%")

You can then add a measure to work out the Sum?