Forum Discussion
Display rows only equal to a specific sum amount
Hi All,
I'm attempting to create a visual that will display material numbers only when the sum value of accounting transactions is equal to 0, representing the fact that debits and credits for that material are equal. This can be easily accomplished by filtering on the sum of currency on a visual, but the issue that presents is the user wants to be able to click on any given row (including the total row) of the table to see what accounting documents are included in the sum value of 0. If I simply use a filter on a visual the total column of the visual will still return entries not equal to 0.
My data looks as below:
| Material | Doc Currency | Accounting Document |
| 1A1 | 10 | 100000001 |
| 1A1 | -10 | 100000002 |
| 2A2 | -20 | 100000003 |
| 3A3 | -15 | 100000004 |
| 3A3 | 20 | 100000005 |
| 4A4 | 50 | 100000006 |
| 4A4 | -50 | 100000007 |
What I'd like would be one visual displaying all materials where sum of doc currency = 0
| Material | Sum of Doc Currency |
| 1A1 | 0 |
| 4A4 | 0 |
| Total | 0 |
which you could then click on any row to filter a second visual of just the accounting documents that make up that row, including the total row.
I believe what needs to be done is build a visual that filters out rows where sum of currency isn't equal to 0 ahead of time. Any thoughts on this?
8 Replies
camargos88Community Champion
Hi Anonymous ,
Create this measure Filter_0 and filter the visual when the value is 0, like:
Thats how I currently have it structured, the only issue being the user wants the ability to click on any row of the table to have another visual display the associated accounting doc. If a filter is applied as you specified when clicking on the total row of "0" all documents are returned even if the sum total is not 0.
- camargos88
Anonymous ,
So you can apply this filter for the entire page. Does that work ?