Forum Discussion

marcofalzone's avatar
marcofalzone
Helper I
3 years ago
Solved

Remove the Row context while keeping Filter context

Hi community,  I have a table like the following, where Comp_x are companies belonging to the same group (owner). Table: "Transactions"     My goal is to write a DAX measure that calculate...
  • sevenhills's avatar
    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!