Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Group aggregated data using date slicer

Hi,

 

I tried to get a solution here: https://community.powerbi.com/t5/Desktop/Remove-0-from-balance-list/m-p/1150852

 

But I try to reframe:

 

Is it possible to create something like a group by that can be altered by a slicer?

 

Let me show you the table [Daten]

 

dateaccount novaluetext
01.01.20201001-50invoice 01
02.01.20201002-70invoice 02
02.01.20201003-20invoice 03
05.01.2020100150payment …
05.01.2020100265payment …
07.01.2020100310payment …

 

It is easy to get the balance list:

 

account noSaldo (sum of value)
10010
10025
100310

 

But I need to get rid of all account numbers that have a "saldo" of 0.

 

A filter is of no use because on row level, there is no 0

 

So I thought it would be helpful if there was a possibility to create a virtual table where all values are grouped by account number. 

And if I change the selected date (slicer) the grouping also changes.

 

It would be easy to create a grouped table, but I want to know the "saldo" in a specific time period. (2019, 2020, 1-5 2020... )

 

do you think this is possible?

thanks a lot!

  • Anonymous add another measure that will filter out the zeros

     

    No Zero Measure = 
    SUMX ( VALUES ( Table[Account] ), IF ( [Sum Amount Measure] > 0, [Sum Amount Measure] ) )

     

    I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.

2 Replies

  • Anonymous add another measure that will filter out the zeros

     

    No Zero Measure = 
    SUMX ( VALUES ( Table[Account] ), IF ( [Sum Amount Measure] > 0, [Sum Amount Measure] ) )

     

    I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.

    • Anonymous's avatar
      Anonymous
      Not applicable

      Thanks, it worked. 

       

      In my case there where some account numbers with a "saldo" of 0,00000002 (I really don't know how that is even possible... ), 

      thats why nothing worked!