How to use SUM and LOOKUP dynamically

hyudolee
hyudolee
in Modeling

I have Order module(Region, Order)

Order(1,2...) comes from each Region. Order has Item and Amount.

hyudolee_8-1638413574469.png

I want to aggregate item-amount by dynamic Region like this table.

hyudolee_0-1638413941567.png

 

If Users chose the Europe, Item-Amount Table is aggregated by Europe.

hyudolee_1-1638413959288.png

I tried SUM and LOOKUP but I couldn't.

 

The point is I don't need Region List in Item-Amount table because it consumes memories.

And I don't want to use List Subset function because dynamic region setting has another dimension.

( I omitted the instructions to explain briefly)

Tagged:

Best Answer

  • Rashmih
    Rashmih
    Answer ✓

    Hi @hyudolee ,

     

    Please add a line-item in the Order module to filter Amount based on Dynamic Region for example as shown below...

    Rashmih_2-1638421773420.png

     

    and then use this in Item-Amount Module to calculate the sum

     

    Rashmih_3-1638421798040.png

    Hope this helps...

     

     

     

     

Comments

  • Misbah
    Misbah

    @hyudolee 

     

    Typical use case of SUM and SELECT or even SUM and LOOKUP. Since these compound functionalities need to be avoided due to Scalability and Performance issues I think Region dimension needs to be added in order for this to work.

    Also, instead of giving the end user the flexibility to pick from pick lists, why don't use context selectors 

     

    Thanks,

    Misbah

    Miz Logix

  • bhill5
    bhill5

    Can you use the "dynamic selector" to create a dynamic filter on a saved view of the data table?

Categories