Forum Discussion
Problem with DAX formula
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.
