Forum Discussion

Axel_hnk's avatar
Axel_hnk
Frequent Visitor
4 years ago
Solved

SUMX with Lookup

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] ProductIdDa...
Need Help
  • selimovd's avatar
    selimovd
    4 years ago

    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

     

     

selimovd's avatar
selimovd
Most Valuable Professional
4 years ago

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's avatar
Axel_hnk
Frequent Visitor
4 years ago

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