Adding a value to DAX formula breaks my filter context

[davidoz]

I have an absolutely maddening powerbi issue which I am having trouble resolving

I have a measure called CB COST PLUS PCT which is trying to calculate the percentage we marked up "CB" expenses. As a starting point, if I define the measure as:

CB COST PLUS PCT = [CB REVENUE] / [CB EXPENSE]

Everything works just fine. Here is an example of my matrix below with the values hidden

However, the measure is a percentage, so really the correct measure is

CB COST PLUS PCT = [CB REVENUE] / [CB EXPENSE] - 1

When I adopt this measure, everything breaks. Because the "-1" makes every date, every job, every account etc... equal to -100%, then now there is a value for every one of those things and I can't make it go away. See example below:

List of things I have already tried with unsuccessful results:

• Filtering the page, and visuals to exclude anything where CB P&L is blank or equal to zero
• Filtering the page, and visuals to exclude anything where CB COST PLUS PCT = -1
• Making the measure null, or "" if equal to -1
• Incorporating CALCULATE / FILTER

I have absolutely no idea where to go from here. It is such a simple problem and I need it to work but I can't figure it out

[CoreyP · Accepted solution]

Have you tried something like:

IF( OR( ISBLANK( [CB REVENUE] ) , ISBLANK( [CB EXPENSE] ) ) , BLANK() , [CB REVENUE] / [CB EXPENSE] - 1 )