Forum Discussion
Remove the Row context while keeping Filter context
- 3 years ago
Try this for Internal Transactions, it will match the Laboratories example, but NOT match the Engineering as I don't see the data for Engineering + Cost Personnel in your example.
Internal Transactions = var _selPL = SELECTEDVALUE(TableTransactions[Product Line]) var _selPLRow = SELECTEDVALUE(TableTransactions[P&L_Row]) RETURN CALCULATE( SUM('TableTransactions'[Amount]), FILTER( ALLSELECTED(TableTransactions), TableTransactions[Product Line] = _selPL && TableTransactions[P&L_Row] = _selPLRow && CONTAINSSTRING(TableTransactions[Company], "Comp") && CONTAINSSTRING(TableTransactions[Counterpart], "Comp") ) )For the Internal Eliminations,
* Are you trying to show the same value for Costs* rows as Internal Transactions value?
* For Revenue Services rows, are you just negating this above calculated sum of all costs rows value?* For the rest of the rows, you are showing as zero... Kind of not clear!
Tried some DAX to get what you need ...
Internal Eliminations = var _selPL = SELECTEDVALUE(TableTransactions[Product Line]) var _selPLRow = SELECTEDVALUE(TableTransactions[P&L_Row]) var _selCompany = SELECTEDVALUE(TableTransactions[Company]) var _selCounterpart = SELECTEDVALUE(TableTransactions[Counterpart]) var _Tmp1 = ABS( CALCULATE( SUM('TableTransactions'[Amount]), FILTER( ALL(TableTransactions), TableTransactions[Product Line] = _selPL && CONTAINSSTRING(TableTransactions[P&L_Row], "Costs") ) )) RETURN SWITCH (TRUE() , CONTAINSSTRING(_selPLRow, "Costs"), [Internal Transactions] , CONTAINSSTRING(_selPLRow, "Revenues Services"), _Tmp1 , 0)Hope this helps or tune to your requirements!
Dear sevenhills , I really appreciate your effort. Thank you very much for your kind and precise reply.
I do apologize for this late reply, I've been travelling this week. I'm going to have a deep dive into your message, and I'll be back with a feedback within this weekend.
Thanks again for your contribution, I do really appreciate!
Marco - Monterrey, Mexico.