Remove columns with zero sum values in a matrix (P&L)

[MVCPA]

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:

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 Result

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. 

[jdbuchanan71]

OK, but did the trick with the measure work?

Return DIVIDE(Result,Result) * Result

[MVCPA]

I would like to attach a sample pbix for future referrence but it is not letting me saying it is not supported. 😞

[MVCPA]

Yes it did and it worked like magic! Thank you! I really appreciate your help!

[jdbuchanan71 · Accepted solution]

@MVCPA 

Not sure if this will work but give it a try.  You are using 
RETURN Result

There 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

[MVCPA]

Thank you for your reply. I am actually getting the problem from this line and not from the other lines: 

 Calctype = 1, [SUM AMOUNT],