Remove 0 from balance list

[herbemischung]

Hi,

I created a balance list. like that:

account no. / balance

12345 / 50€

12346 / 70€

12347 / 0€

12348 / -15€

I want to remove all account numbers with a blance of 0

Im that case the 0 of the account no 12347 consists of the following cases:

1.1. Invoice +50€

5.1. Payment -50€

So, if I filter balance (is not 0) then nothing happens because on row level there is no 0.

I want to exclude all Account Numbers if the sum of balance (invoice + / payment-) equals 0

And I have absolutely no clue how to do that! Hope you guys are smarter 😉 

thanks!

all the best, herbert

[v-lionel-msft]

Hi @herbemischung ,

You need to sum up the flow of each account first, so that you can determine which account balance is 0.
Please give a sample data.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[herbemischung]

Hi, unfortunately I am not allowed to provide our company data

[herbemischung]

I wonder if it would be possible to create a dynamic table.

Something like groupby. Table = groupby([Daten] (account number), [Daten], (Sales Amount) --> I know that this is not accurate 😉

I imagine a Table that is grouped by "account number" and "sales amount" but dynamic in terms of Time.

[v-lionel-msft]

Hi @herbemischung ,

Has your problem been solved?

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[herbemischung]

yeah, but the main problem was that there have been values like 0,0000002 - I really don't know how that was even possible, but, all the zeros I thought are wrong havent been zeros at all. Thats why everything failed

[v-lionel-msft · Accepted solution]

Hi @herbemischung ,

Maybe you can refer to the following method.

I constructed such a table.

Then, I created a calculated table.

Table = 
SUMMARIZE(
    Sheet1,
    Sheet1[account no.],
    "Balance", SUM(Sheet1[balance] )
)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@herbemischung , can you share formula you are using

calculate(sum(Table[balance]),filter(Table,Table[balance]<>0))

[herbemischung]

Thank you, but it didn't work like that. I still had a lot of Zeros.