Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Hi, @TeigeGao
Please find attached a part of the data and two screenshots of what I mean.
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.
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.
Hi @lekkerbek ,
Assume that we have a table like below:
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:
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |