Problem with DAX formula

Question

I have created a a measure with a DAX formula that estimates the remaining mandays of a project based on the completion percentage.

My DAX formula is the following:

"Estimated Remaining MD = (1 - SUM([Completion%])) * SUM([ProposedMD])"

Basically I'm calulating the remaining percentage to complete the project and I multiply it with the proposed man days from the project plan.

The percentage number is between and 1 and I convert it to % within Power BI by changing the type.

Mostly this works but for some reason for some departments I get some weird numbers like -3000 man days, which is not possible since the man days can't be a negative number as you can see from the DAX formula.

At first I thought that the problem might be some missing data since some projects don't have completion% or proposedMD data. But those projects naturally return a blank from my formula. plus all my departments have some missing data and in some departments I get the correct numbers and in some others not.

I tried to pinpoint a single project that may b the issue but when I filter for each project separately all the numbers are fine and the projects that have missing data appear blank.

My guess is that something goes wrong during the summarising when I view the summary of projects per department.

Also another thing that I noticed is that when I create a table with the projects and enter the project name , Completion% and proposedMD I can see all the projects even those with missing data. But when I put on the table the estimated remaining MD measure I have created all the projects with missing data dissappear. Why is that? Shouldn't all the projects still show but with blanks where data is missing?

greg_deckler · Answer

Can you post some sample data, especially one where you are seeing weird behavior?

First, your DAX formula does not prevent ProposedMD from being negative. The sum of -400 and -300 woudl be -700 for example. Second, I suspect that it is your SUM for Completion% that might be causing the problem. If the SUM is greater than 1, you are going to get a negative number as your output. I do not know why you are summing Completion% because I do not know your data, but I would suggest maybe MIN or MAX or AVERAGE.

As for the last part, putting a measure in a visualization basically creates an automatic context filter for that visualization to where the measure is a valid, non-blank value. However, this is generally the case with both columns and measures in a table. It is even the case if you set the filters for that column or measure to "is not blank" OR "is blank". The general way to fix it is to add a column to the table that always has a value AND is numeric. Don't ask me why but this is what I have found through experimentation. A text field or date field that always has a value won't work.

mork · Answer

Greg_Deckler&nbsp;ok so Completion% is always between 0 and 1. never more than one and I checked that. So it's not possible to have negative outcome.&nbsp;the table I'm using is like te following:&nbsp;ProjectName &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ProposedMD &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Completion% &nbsp; &nbsp; ProjectDpt.&nbsp; &nbsp; a &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.8 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A&nbsp; &nbsp; b &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;25 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;A&nbsp; &nbsp; c &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;B&nbsp; &nbsp; &nbsp;d &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;55 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; C&nbsp;&nbsp; &nbsp; &nbsp;e &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;B&nbsp;etc. you get the point.&nbsp;&nbsp;When I filter for an individual project I get the correct number and I get blank when there is missing data. When I get the data unfiltered (all the projects) or filtered for a specific department I get in some departments the correct numbers and in some others wrong numbers. All the departments have missing data so, missing data is not the cause. Also the data I have for the projects is also correct and it's not something like more tha 100%.&nbsp;&nbsp;As for the table visual, as you can see above my tabl currently shows all projects even those that have missing data. The moment I add the DAX formula as a column the table gets filtered and I can see only the projects that don't have missing data. Why without that columns missing data appears fine and doesn't get filtered and when I add the column the missing data gets filtered?

sean · Answer

morkCreate these 2 COLUMNS and 2 Measures as in the pictures - this should solve it. Let me know.

greg_deckler · Answer

So, the problem is your SUM for Completion%. When you are looking at all of the data and grouping by ProjectDpt, for example, if you have 2 projects that are both 0.8 complete, that SUM is going to give you 1.6. 1 - 1.6 = -0.6 and hence the negative numbers.

mork · Answer

Greg_DecklerYeah that sounds like it is the problem. The thing though is that when I remove the SUM I get this error:&nbsp;"A single value for column "Completion%" in table ----- cannot be determined. This can happen when a measure formula refers to a colum that contains many vlaues withouth specifying an aggregation such as min, max, count, or sum to get a single result."&nbsp;&nbsp;Sean&nbsp;Sean your solutions uses columns and I would like to use a single measure if that's possible.&nbsp;

Forum Discussion

Problem with DAX formula

7 Replies

Related content

Measure formula problem

If formula

formula

DAX Formula Help

Problem with Formula