Forum Discussion

giacomobonetti's avatar
giacomobonetti
Frequent Visitor
9 years ago
Solved

Error when creating a calculated column

I want to create a calculated column out of the division of a column, which is in one table, and a calculated column which is in another table.

 

The formula I am using is: 

 

Column = -DIVIDE(ExpensesData[TOT Expense];SalesData[Total Revenue])

 

The problem arises when I type in the name of the second column (SalesData[Total Revenue]), and the error says: "A single value for column 'Total Revenue' in table 'SalesData' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

    Probably need to use RELATED or RELATEDTABLE in your calculation.

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    Hi giacomobonetti,

    Is there relationship between ExpensesData and SalesData table? Just as Greg_Deckler posted, you should use RELATED function. Could you please share the sample data or screenshot for further analysis?

     

    Best Regards,

    Angelia

    • giacomobonetti's avatar
      giacomobonetti
      Frequent Visitor
      9 years ago

      Thank you both for your help. I solved the problem by creating new columns directly in the dataset, although I will keep in mind the function in case I find myself in the same situation again.

       

      Cheers,

      Giacomo