Returning a 0 for Negative Number- but giving positive numbers and totaling without the negative

[alpeytongreene]

I am working on a vacancy rate for my organization and we are going to be using an adjusted vacancy (if over budgeted they want to show 0 instead for the negative number). I have the 0 showing and the numbers showing- but it is still adding in the totals. 

My formulas are: 

Adj Vacancy = IF(Recruitment_Emp_List[Vacancy]<0,0, [Vacancy])

Adj. Vacancy Rate = DIVIDE([Adj Vacancy], [Budget FTE Hours divided by Full FTE])

Snip of a table I am using to look at the data. The adjusted Vacancy should read about 1,518.18 and the adjusted rate would change to between 12% (I believe). 11% was the overall including the negative. 

[PaulOlding]

How about

Adj Vacancy =

VAR _PositiveVacancy =

CALCULATE(

       SUM(Recruitment_Emp_List[Vacancy]),

       Recruitment_Emp_List[Vacancy] > 0

)

VAR _Result = COALESCE([Adj Vacancy], 0])

RETURN

         _Result

Adj. Vacancy Rate Measure would stay as you have it.

[alpeytongreene]

Good Morning Paul- 

I am still getting the error on the Vacancy part. 

[PaulOlding]

The row is COALESCE hasa mistake.  Let's try this one.

Adj Vacancy =

VAR _PositiveVacancy =

CALCULATE(

       SUM(Recruitment_Emp_List[Vacancy]),

       Recruitment_Emp_List[Vacancy] > 0

)

VAR _Result = COALESCE(_PositiveVacancy , 0])

RETURN

         _Result

[alpeytongreene]

I still get an error, could it be because the vacancy is not a column of it's own but a formula I have included?

[PaulOlding]

Ah, ok.  Yes, that would be a problem.  It'll help to see a screenshot of the model and the definition of [Vacancy].

[alpeytongreene]

Hi Paul! Sorry for my delay an urgent project came to my desk and I had to put this aside. The definiton of vacancy for the model is the Budgeted FTE Hours/Full FTE (to get it into an FTE number) - Actual FTE. I am trying to recreate the two documents into one but it is proving a bit more difficult/time consuming than I thought.

[AlB]

Hi @alpeytongreene 

Can you share the pbix?

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[alpeytongreene]

I can't share the pbix. I'm sorry. I am still trying to figure this one out. I am trying to use the sumx suggested below but keep getting an error message because the vacancy is a formula and not a column in the sheet I believe. I can't peel it back further the vacancy is a formula to calculate the difference between the budgeted FTE and actual FTE 

[rfigtree]

It will be because of context. When the total is calculated your if statement won't contain the same context as it does at row level. You will need to use an iterative approach to calculate your measure.

[alpeytongreene]

Can you provide an example, I am a visual person. Sorry. 

[rfigtree]

Sorry I'm on my phone waiting for wife to get up so can't give u much. When the total is calculated. Your if statement becomes if(1752.38<>0,1752.38,0).

The measure needs to use something like sumx. To calculate adjusted vacancy so the if is against each line.

[alpeytongreene]

I keep getting the red line errors. I am using a formula and not a column in my sheets, could that be why? I am sorry I got you in trouble! - Sorry Mrs. rfigtree! 

[rfigtree]

Adj. Vacancy Rate = DIVIDE(sumx(your table,[Adj Vacancy]), [Budget FTE Hours divided by Full FTE])

[rfigtree]

Now I'm in trouble. She got up and glared at me because i am on my phone.