Forum Discussion
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
Solution Sage
Have you tried something like:
IF( OR( ISBLANK( [CB REVENUE] ) , ISBLANK( [CB EXPENSE] ) ) , BLANK() , [CB REVENUE] / [CB EXPENSE] - 1 )