Filter balancing rows (negative and positive = 0)

[Robin9700]

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! 

[v-jingzhang]

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]

@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]

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

[Robin9700]

@v-jingzhang Thank you for this reply. I want to show the balance of a certain ledger account on a certain date. The date slicer needs to be set to this date.

Example: I want to know the balance of the account at September 1st, given the following transactions;

1. August 15, Purchase 1200 EUR

2. August 21, Purchase 650 EUR

3. August 25, Purchase 150 EUR

4. August 30,  Purchase 220 EUR

5. August 25, Payment -650 EUR

6. August 31, Payment -220 EUR

7. September 4, Payment -1200 EUR

8. September 10, Payment -150 EUR

-> Current balance = 0,00 EUR

TOTAL BAL. AT SEPTEMBER 1st = 1350 EUR

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

1. Purchase 1200 EUR

2. Purchase 150 EUR
TOTAL BAL. AT SEPTEMBER 1st = 1350 EUR

[v-jingzhang · Accepted solution]

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.

[Robin9700]

Hi @v-jingzhang , thanks again for your reply. Unfortunately, the measure still doesn't give me the expected result. I have looked into the DAX-expression and did some research, but I don't really understand where the problem originates. Do you have any other ideas? Or maybe mr. @Greg_Deckler is able to help? If not, that's also fine of course. 

Thanks in advance!

[v-jingzhang]

Hi @Robin9700 Can you share some sample data to show the current problem?

Actually there is a scenario that I don't know how to deal with:

if data occurs like 

1. Accruals - Purchase;   4.500 EUR

2. Accruals - Purchase;  1.000 EUR

3. Accruals - Payment;  -5.500 EUR

Then balance = 0.000

In above scenario, if you want all three rows to not display as the balance is 0, it would be difficult as there may be other data occurring later like -4.500 EUR which can match the first row. My previous measure didn't deal with this scenario. Not sure if this is the cause. 

Jing

[Robin9700]

Hi @v-jingzhang , thank you again for your reply. This scenario appears a few times in my data, but is not the cause for my problem (I understand that the measure doesn't work like that, so I don't expect it to). What happens is that the measure, when set to = 1, still returns all lines. So also the lines like

1. Purchase 1000 EUR
2. Payment -1000 EUR  

are still showing up, even though the measure is supposed to rule them out.

[v-jingzhang]

Can you share a sample pbix so that I can work on that? Remove sensitive info before sharing it. How to provide sample data in the Power BI Forum

[Greg_Deckler]

@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]

@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

[Greg_Deckler]

@Robin9700 OK, one more question, do you have a date column or an index column?

[Robin9700]

@Greg_Deckler  No I don't. I do have a date-slicer applied to the matrix.

Edit: In the data source (imported table) there is a date-column; just not in the visual I'm trying to create.

[Greg_Deckler]

@Robin9700 OK, I doubt this will work for complex scenarios (or at all) but maybe it can be improved:

Measure = 
  VAR __Date = MAX('Table'[Date])
  VAR __Amount = MAX('Table'[Amount])
  VAR __NegAmount = -1 * __Amount
  VAR __Sign = SIGN(__Amount)
  VAR __CountPos = COUNTROWS(FILTER(ALL('Table'),[Amount] = __NegAmount) && [Date]>__Date))
  VAR __CountNeg = COUNTROWS(FILTER(ALL('Table'),[Amount] = __Amount) && [Date]<__Date))
RETURN
  SWITCH(TRUE(),
    __Sign = 1 && __CountPos > 0,BLANK(),
    __Sign <> 1 && __CountNeg > 0,BLANK(),
    __Amount
  )