Forum Discussion

mork's avatar
mork
Helper V
10 years ago

Problem with DAX formula

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 = ...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
10 years ago

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's avatar
mork
Helper V
10 years ago

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's avatar
    v-sihou-msft
    Microsoft Employee
    10 years ago

    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,