Forum Discussion

AliceW's avatar
AliceW
Icon for Power Participant rankPower Participant
5 years ago
Solved

A Sum Column in one table from another table's values

Hi ladies and gents,

This sounds simple enough, right? And yet I am going back and forth for the past hour. Please help!

So, here it goes

- Table OPPS with the column OPP ID

is in a one-to-many, bi-cross filter relationship with

 - Table OPP LINES, with the columns OPP ID, PRODUCT, AMOUNT

I need a column (the measure works fine) in the OPPS table with the AMOUNT per OPP.

I did this

AMOUNT per Opp =
calculate(
sum('OPP LINES'[AMOUNT]),
filter('OPP LINES',OPPS[OPP ID]='OPP LINES'OPP ID])
)
It works. However, the PRODUCT doesn't work anymore as a filter! It will change, but it will include all the Amount for an Opp which has that product (so all the lines). I need the amount to change based on it.
Any suggestions, please?
Big thanks in advance,
Alice
Need Help
  • MattAllington's avatar
    MattAllington
    5 years ago

    Ironically, I have added 2 new chapters to the third editon, and one is on RANKX. You don't need a column - you can create a virtual column/table. Something like this

     

    =rankx(all(Opps),calculate(sum('OPP LINES'[AMOUNT])))

     

    the ALL(ops) portion behaves like the calc column you mention but it is done in memory at runtime. It has the added benefit of reacting to the filter on product. 

5 Replies

  • MattAllington's avatar
    MattAllington
    Icon for Community Champion rankCommunity Champion
    5 years ago

    You say you need a column. Why do you think you need a column and not another measure?
    then you say product doesn't work as a filter. What does that mean?

    your column should work like this (even though it is unlikely to be the best approach).
    =calculate(sum('OPP LINES'[AMOUNT]))

    • AliceW's avatar
      AliceW
      Icon for Power Participant rankPower Participant
      5 years ago

      Hi Matt!

      Great question. I'm trying to do a RANX in the Opp table, so I need a column to base it on.

      Also, I read you book, 'How to write Dax'; beautiful work!

      Alice

      • MattAllington's avatar
        MattAllington
        Icon for Community Champion rankCommunity Champion
        5 years ago

        Ironically, I have added 2 new chapters to the third editon, and one is on RANKX. You don't need a column - you can create a virtual column/table. Something like this

         

        =rankx(all(Opps),calculate(sum('OPP LINES'[AMOUNT])))

         

        the ALL(ops) portion behaves like the calc column you mention but it is done in memory at runtime. It has the added benefit of reacting to the filter on product.