Forum Discussion

davidoz's avatar
davidoz
Frequent Visitor
2 years ago
Solved

Adding a value to DAX formula breaks my filter context

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

  • Have you tried something like:

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

1 Reply

  • CoreyP's avatar
    CoreyP
    Icon for Solution Sage rankSolution Sage

    Have you tried something like:

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