Forum Discussion

Robin9700's avatar
Robin9700
Icon for Helper I rankHelper I
5 years ago
Solved

Filter balancing rows (negative and positive = 0)

Hi all,

I need a way to filter out data ( EUR-amounts) which balance each other out, in a matrix. I.e. in our ledger entries, the follwing information might occur:

1. Accruals - Purchase;   4.500 EUR

2. Accruals - Purchase;  1.250 EUR

3. Accruals - Payment;  -4.500 EUR

The first and last row cancel each other out (balance = 0,00), so I only want to see the 2nd row, as this would be the open balance on the Accruals-account. Is there any way I could perform this?

Note; row 1 and 3 have different descriptions and/or documentnumbers.

Thanks for your help! 

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    Hi Robin9700 

     

    Try this measure. Note that dates in the date slicer should come from a table that's disconnected from other tables. 

    Flag Measure = 
VAR _selectedDate = MAX('Slicer Dates'[Date])
VAR _positiveTable = FILTER(ALL('Table'),'Table'[Date]<=_selectedDate && 'Table'[Absolute Value]=SELECTEDVALUE('Table'[Absolute Value]) && 'Table'[Sign]=1)
VAR _negativeTable = FILTER(ALL('Table'),'Table'[Date]<=_selectedDate && 'Table'[Absolute Value]=SELECTEDVALUE('Table'[Absolute Value])&&'Table'[Sign]=-1)
VAR _positiveCount = COUNTROWS(_positiveTable)
VAR _negativeCount = COUNTROWS(_negativeTable)
VAR _diff = _positiveCount - _negativeCount
RETURN
SWITCH(TRUE(),
_diff=0,0,
_diff>0,VAR _t = SELECTCOLUMNS(TOPN(_diff,_positiveTable,[ID],DESC),"DisplayID",[ID]) RETURN IF(SELECTEDVALUE('Table'[ID]) IN _t, 1, 0),
_diff<0,VAR _t = SELECTCOLUMNS(TOPN(_diff*(-1),_negativeTable,[ID],DESC),"DisplayID",[ID]) RETURN IF(SELECTEDVALUE('Table'[ID]) IN _t, 1, 0),
0)

    Download the new pbix file for details.

     

    Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as the solution to help other members find it.

14 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Robin9700 So the only criteria is that the numbers are equal but opposite? What if there is one 4.5 number and two -4.5 numbers?

    • Robin9700's avatar
      Robin9700
      Icon for Helper I rankHelper I
      5 years ago

      Greg_Deckler Hi, thanks for the reply! In that case, I would like to see a balance of -4.50. So basically, I only want to see the numbers that form the current balance, any other amounts should be hidden. Another example:

       

      1. Purchase 1200 EUR

      2. Purchase 650 EUR

      3. Purchase 150 EUR

      4. Purchase 220 EUR

      5. Payment -650 EUR

      6. Payment -220 EUR

      TOTAL BAL. 1350 EUR

       

      In this case, I want to see the following in my matrix:

      1. Purchase 1200 EUR

      2. Purchase 150 EUR
      TOTAL BAL. 1350 EUR

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Robin9700 

     

    Not sure what your data looks like in the datasource, I would like to transform them to have below columns in Power Query Editor first. 

     

    Then add two columns [Absolute Value] and [Sign] to the table based on [Amount] column. Just like below picture.

    Absolute Value = ABS('Table'[Amount])

Sign = SIGN('Table'[Amount])

     

    Then create this measure and drag it into visual-level filter pane of this table visual. Set its value is 1.

    Flag Measure = 
VAR _positiveTable = FILTER(ALL('Table'),'Table'[Absolute Value]=SELECTEDVALUE('Table'[Absolute Value])&&'Table'[Sign]=1)
VAR _negativeTable = FILTER(ALL('Table'),'Table'[Absolute Value]=SELECTEDVALUE('Table'[Absolute Value])&&'Table'[Sign]=-1)
VAR _positiveCount = COUNTROWS(_positiveTable)
VAR _negativeCount = COUNTROWS(_negativeTable)
VAR _diff = _positiveCount - _negativeCount
RETURN
SWITCH(TRUE(),
_diff=0,0,
_diff>0,VAR _t = SELECTCOLUMNS(TOPN(_diff,_positiveTable,[Date],DESC),"DisplayID",[ID]) RETURN IF(SELECTEDVALUE('Table'[ID]) IN _t, 1, 0),
_diff<0,VAR _t = SELECTCOLUMNS(TOPN(_diff*(-1),_negativeTable,[Date],DESC),"DisplayID",[ID]) RETURN IF(SELECTEDVALUE('Table'[ID]) IN _t, 1, 0),
0)

     

    And you will get the result you want. I attached the pbix for your reference.

     

    Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as the solution to help other members find it.

    • Robin9700's avatar
      Robin9700
      Icon for Helper I rankHelper I
      5 years ago

      v-jingzhang Thank you for your reply! Unfortunately, the measure does not return the right values; only the same 3 faulty values keep showing. I am quite unexperiences with measures etc., so I am not sure as to why it is not working properly. This might have to do with the applied date-slicer(?) 


      Could you give me any advice please?

      • v-jingzhang's avatar
        v-jingzhang
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi Robin9700 

         

        That may be the cause. If you select dates in the date slicer, it will apply the date filter to the measure and the table visual. Do you want the date slicer to influence the result in the table? If so, what is the expected output? Can you provide more details about it?

         

        There will be different solutions according to different requirements. Sample data and explanations are appreciated if there are various conditions need to be considered. It will help me understand your scenario better.

         

        Regards,
        Jing