Problem with DAX formula

[mork]

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]

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]

@Greg_Deckler

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.

the table I'm using is like te following:

ProjectName          ProposedMD          Completion%     ProjectDpt.

    a                                  20                          0.8                   A

    b                                  25                                                  A

    c                                                                 0.2                  B

     d                                55                            1                     C 

     e                                3                                                      B

etc. you get the point.

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%.

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?

[Greg_Deckler]

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]

@Greg_Deckler

Actually no, now that I took another look at my data I get wrong numbers even when in my data all competion percetages are different.

[mork]

@Greg_Deckler

Yeah that sounds like it is the problem. The thing though is that when I remove the SUM I get this error:

"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."

@Sean

Sean your solutions uses columns and I would like to use a single measure if that's possible. 

[v-sihou-msft]

@mork

In DAX, it's quite similar between calculated column and measures. But we can't specify single row value in measures. You have to aggeragte your column values. However, in this scenario, you need to calculate first then aggreage. If you use measures, it will aggreagte first then do the calculation so that you will get non-sense result on Total row. So it's better to use calcualted column.

remaining MD = SUM(Sheet6[ProposedMD])*(1-SUM(Sheet6[Complention]))

remaining Completion% = 1-SUM(Sheet6[Complention])

For choosing calculated column or measure, please refer to article below:

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

Regards,

[Sean]

@mork

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