Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
herbemischung
Resolver I
Resolver I

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!

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@herbemischung 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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

2 REPLIES 2
parry2k
Super User
Super User

@herbemischung 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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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!

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.