Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Remove 0 from balance list

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

Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi Anonymous ,

     

    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.

     

8 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , can you share formula you are using

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

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

  • v-lionel-msft's avatar
    v-lionel-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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.