Forum Discussion
Remove columns with zero sum values in a matrix (P&L)
Hi everyone,
I am having hard time trying to remove columns in my matrix table for my P & L.
As you can see in this image, Sites A, B, D and G are all zero's resulting from a Sum of figures and I want to remove them.
Here's my Measure:
I would really appreciate if someone can help me figure this out. I tried using the proposed solutions from this article but I am unsucessful still.
Not sure if this will work but give it a try. You are using
RETURN ResultThere is a trick with DIVIDE where, when you divide by 0 it returns a null instead of a 0 so you can use
Return DIVIDE(Result,Result) * Result
null * 0 is null
The whole measure would look like this.
Actual = Var Calctype = SELECTEDVALUE('Header Assignment'[CalcType]) Var Displaydetailcode = SELECTEDVALUE('Header Assignment'[Detail]) Var IsSubheaderVisible = ISFILTERED('Accounts Name2'[Account Name]) Var IsBracketVisible = ISFILTERED(Bracket[Bracket]) Var IsPeriodVisible = ISFILTERED('Date Table'[Year Month]) Var Result = SWITCH(TRUE(), isSubheaderVisible=True() && DisplayDetailCode = 0 ,BLANK(), isSubheaderVisible=True() && IsBracketVisible =TRUE() && DisplayDetailCode = 0 ,BLANK(), IsBracketVisible =TRUE() && DisplayDetailCode = 0 ,BLANK(), Calctype = 1, [SUM AMOUNT], Calctype = 2, [Running Total A], Calctype = 3, DIVIDE([Running Total A],[Rev amount 2],0) ) Return DIVIDE(Result,Result) * Result
5 Replies
- jdbuchanan71
Super User
Not sure if this will work but give it a try. You are using
RETURN ResultThere is a trick with DIVIDE where, when you divide by 0 it returns a null instead of a 0 so you can use
Return DIVIDE(Result,Result) * Result
null * 0 is null
The whole measure would look like this.
Actual = Var Calctype = SELECTEDVALUE('Header Assignment'[CalcType]) Var Displaydetailcode = SELECTEDVALUE('Header Assignment'[Detail]) Var IsSubheaderVisible = ISFILTERED('Accounts Name2'[Account Name]) Var IsBracketVisible = ISFILTERED(Bracket[Bracket]) Var IsPeriodVisible = ISFILTERED('Date Table'[Year Month]) Var Result = SWITCH(TRUE(), isSubheaderVisible=True() && DisplayDetailCode = 0 ,BLANK(), isSubheaderVisible=True() && IsBracketVisible =TRUE() && DisplayDetailCode = 0 ,BLANK(), IsBracketVisible =TRUE() && DisplayDetailCode = 0 ,BLANK(), Calctype = 1, [SUM AMOUNT], Calctype = 2, [Running Total A], Calctype = 3, DIVIDE([Running Total A],[Rev amount 2],0) ) Return DIVIDE(Result,Result) * ResultThank you for your reply. I am actually getting the problem from this line and not from the other lines:
Calctype = 1, [SUM AMOUNT],
- jdbuchanan71
OK, but did the trick with the measure work?
Return DIVIDE(Result,Result) * Result
