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!
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!