Forum Discussion
Power ParticipantA Sum Column in one table from another table's values
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.
Community ChampionYou 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]))
AliceWHi 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
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.
- AliceW
So cool that you answered! I have the 2016 version, and I just checked, no RANKS chapters ;0)
About my problem, it does work! May I ask though how would you alter the formula so that the ranking changes based on the selected PRODUCT?
Right now, the ranking will show numbers, say, 1, 3, and 4 if the Product corresponding to the 2nd position is deselected. I hope I'm making sense.
I tried wrapping it up win a Calculate and use Allselected(Product), but nothing...
Big thanks again,
Alice