Forum Discussion
Problem with DAX formula
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.
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?
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.
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 your solutions uses columns and I would like to use a single measure if that's possible.
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,
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.
