Filter GL accounts where total sum is zero

[lekkerbek]

Hi all,

I've created a table with the following details

- GL accountnumber

- date

- description

- amount

It shows a subtotal.

Is there a way I could filter the GL accounts where the total sum is zero?

For example:

GL account 1000

1-1-2019 Description € 100

2-1-2019 Description € 200

3-1-2019 Description -/- € 300

Total sum € 0

Even though there are individual lines, the total sum is zero and therefore I would not like to see this GL account number in the list.

Is this somehow possible?

[lekkerbek]

Hi, @TeigeGao 

Please find attached a part of the data and two screenshots of what I mean.

The goal is to create a matrix with just the projects that have a total sum amount that is not zero (active projects). So basically PRJ001, PRJ005 and PRJ022 should not be visible as the total sum is zero. The next screenshot is a further drilldown of the data.

Is this possible? Another option would be to add a column where I definine the project to be activa or not and filter by that.

[TeigeGao]

Hi @lekkerbek ,

Assume that we have a table like below: 

Then we can create a measure like below: 

Measure =
CALCULATE (
    SUM ( 'Table'[amount] ),
    FILTER ( ALL ( 'Table' ), 'Table'[GL account] = MIN ( 'Table'[GL account] ) )
)

The measure will calculate the sum of amount for all account, then we can draqg this measure to visual level filter:

The total sum € 0 will be filtered.

Best Regards,

Teige

[lekkerbek]

Thanks @TeigeGao ,

I wasn't able to make it work yet, but I will try again tomorrow and provide an actual screenshot of the data which may help.