topic Re: Returning a 0 for Negative Number- but giving positive numbers and totaling without the negative in DAX Commands and Tips

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

Anonymous — Mon, 10 May 2021 15:11:38 GMT

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.

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

rfigtree — Fri, 07 May 2021 22:58:42 GMT

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.

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

Anonymous — Fri, 07 May 2021 23:03:56 GMT

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

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

rfigtree — Fri, 07 May 2021 23:30:53 GMT

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.

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

rfigtree — Fri, 07 May 2021 23:33:45 GMT

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

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

AlB — Sat, 08 May 2021 17:13:19 GMT

Hi Anonymous

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.

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

Anonymous — Mon, 10 May 2021 15:02:45 GMT

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

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

Anonymous — Mon, 10 May 2021 15:09:11 GMT

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!

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

PaulOlding — Mon, 10 May 2021 16:57:36 GMT

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.

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

Anonymous — Mon, 10 May 2021 17:48:14 GMT

Good Morning Paul-

I am still getting the error on the Vacancy part.

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

PaulOlding — Mon, 10 May 2021 18:14:21 GMT

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

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

Anonymous — Mon, 10 May 2021 20:21:14 GMT

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?

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

rfigtree — Tue, 11 May 2021 06:50:49 GMT

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

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

PaulOlding — Tue, 11 May 2021 08:22:56 GMT

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

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

Anonymous — Tue, 18 May 2021 19:29:50 GMT

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.