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.
Power ParticipantHi 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
Community ChampionIronically, 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
- MattAllington
The ALL inside the RANKX tells the formula to rank the product in the visual against all products. If you want a slicer on Products and you want your visual to show then rank only against the products selected in the slicer, simply replace the first ALL with ALLSELECTED. No additional calculate needed.