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
lekkerbek
Helper IV
Helper IV

Filter GL accounts where total sum is zero

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?

3 REPLIES 3
lekkerbek
Helper IV
Helper IV

Hi, @TeigeGao 

 

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

 

Screenshot 3.PNG

 

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.

 

Screenshot 1.PNGScreenshot 2.PNG

 

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
Solution Sage
Solution Sage

Hi @lekkerbek ,

Assume that we have a table like below: 

PBIDesktop_HtK2rL9wZJ.png

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:

PBIDesktop_U4dHdOgbr1.png

The total sum € 0 will be filtered.

Best Regards,

Teige

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.

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.