Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Calculate column gives wrong result

I have set a measure in Table1 to the sum of a column in Table 2 and I get the correct value in the measure in Table1. 

'Table1'[Measure] = sum('Table2'[column])

I then set a calculated column in Table1 to that measure I just created in Table1 and I get a larger value in the calculated column.

'Table1'[column] = 'Table1'[measure]

Table1 and Table 2 are linked in manage relationships

 

What is happening and how do I resolve this. 

 

Thanks

Need Help

5 Replies

  • Selva-Salimi's avatar
    Selva-Salimi
    Solution Sage
    1 year ago

    Hi Anonymous 

     

    I dont know why did you do that. but you can write a column as follows:

    calculate(sum( table2[value] , filter (table2, table2 [Id] = table1 [Id]))

    * Id is the column that these two table have relation on

     

     
    If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly. 
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    I tried the formula you provided. Unfortunately it rendered the same result as before.

    'Table1'[Measure] = sum('Table2'[column] which rendered 100

    calculate(sum( 'Table2'[column] , filter (table2, table2 [Id] = table1 [Id]))  which  rendered 180

     

    Any other thoughts?

    • Selva-Salimi's avatar
      Selva-Salimi
      Solution Sage
      1 year ago

      So, the relation might be incorrect. is the relation "Both" or "Single" cross-filter direction?

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        I tried it with the cross filter set to single and both. It didn't make a difference in the result.

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous ,

    Based on the description, if Table1 have duplicate row values, you will get a larger value in the calculated column. What’s more, measure and calculated column act differently.

    So, you can calculate the column sums in Table 2 using only the measure.

    Table1 Measure = calculate(sum('Table2'[Number]) , FILTER(Table1, Table1[ID] = RELATED(Table2[ID])))

    You can also view the following documents to learn more information.

    The Key Differences between a Measure and a Calculated Column in Power BI (everythingpowerbi.com)

    Measure vs Calculated Column: The Mysterious Question? Not! - RADACAD

     

    Best Regards,

    Wisdom Wu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.