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]

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

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

     

     

4 Replies

  • 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
              )
         )
      )
  • Signore_Ands's avatar
    Signore_Ands
    Advocate III
    4 years ago

    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?


  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    4 years ago

    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