Display rows only equal to a specific sum amount

[Anonymous]

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?

[camargos88]

Hi @Anonymous ,

Create this measure Filter_0 and filter the visual when the value is 0, like: 

[Anonymous]

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 ?

[Anonymous]

the page level filter removes all rows as it's looking for entries with a value of 0 as opposed to a sum of 0, it doesn't appear that works either.

[harshnathani]

Hi @Anonymous ,

Measure 5 =

var dc = CALCULATE(SUM('Table'[Doc Currency]),ALLEXCEPT('Table','Table'[Material]))

REturn

IF(dc = 0 , dc)

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

This measure works perfectly to only return the values I'm looking for without page filters, however it appears the "total" row of the visualization will still return ALL values in other visualizations.  Is this simply the standard behavior of power BI to return all values when clicking the totals row as opposed to showing only the values that went into the total?

[v-yingjl]

Hi @Anonymous ,

In table / matrix visual, total can be seen as a single row so that we can click it like other rows to filter our data. It is a standard behavior in power bi desktop. If you don't want to show the total, just disable it in the total menu.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

You can hide the total in the format area of that visual.  Or you can wrap the return in that measure with IF(HASONEVALUE(Table[Material), IF(dc=0, ...

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat