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

[AliceW]

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

[MattAllington]

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]

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 · Accepted solution]

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

[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.