Forum Discussion

siddhantk989's avatar
siddhantk989
Helper III
9 years ago
Solved

Calculated Column is returning blank values

Hi,

 

  I am trying to create a table to show my forecast data. the forecast data is based on category and i have Target % to calcualte the forecast. Sales column is a measure which i am calulating based on some conditions. But when i a trying to create a calcualted column using Sales measure and Target % column I am getting blank values in my Target Sales column. the formula I am using is:

 

 Target Sales = ( 'Table'[Target %] + 1) * [Sales] 

 

But the formula is not working and i am getting blank values in return.

 

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • siddhantk989's avatar
    siddhantk989
    9 years ago

    MFelix and Anonymous 

     

    Found the soultion. I did all the calculation in SQL and then I am bringing my data to Power BI. It is working fine now.

     

    Thanks  alot for supporting guys.

     

    You guys are great.

     

    Thanks,

    siddhant

  • siddhantk989's avatar
    siddhantk989
    9 years ago

    v-huizhn-msft,

     

      It is kind of a temporary solution as I did nothing in Power Bi to resolve it. What I am doing is that instead of creating calculated fields in Power BI I am creating those column in my database itself while fetching the data.

17 Replies

  • MFelix's avatar
    MFelix
    Super User
    9 years ago

    Hi siddhantk989,

     

    The problem is that you have blanks in your Target% in Power BI blanks aren't treated like 0 in excel.

     

    Redo your column to:

     

    Target Sales = (  
                           IF(
                                ISBLANK('Table'[Target %]),
                                0,
                               'Table'[Target %]) + 
                         1) *
                     [Sales]

     

    This should do what you need.

     

    Regards,

    MFelix

    • siddhantk989's avatar
      siddhantk989
      Helper III
      9 years ago

      Hi MFelix

       

        Thanks for the reply but I tried using the IF condition and it is still showing me blank values for Target Sales.

       

        Also I am having one more issue. I am creating another calculated column that tells current month target and i am using Target % column to do the calculation. When i am trying to create the column I am getting an error saying "A circular dependency was detected". Do you have nay suggestion on this one?

       

      Thanks,

      Siddhant

      • MFelix's avatar
        MFelix
        Super User
        9 years ago

        Hi siddhantk989,

         

        Was looking at your information and made a trial and use this to eliminate the blanks:

         

        Target Sales = SUMX(Sales; (Sales[Target %]+1)*Sales[Sales])

        This is comparable to the SUMPRODUCT Function in Excel.

         

        What's the formula you are using for the column?

         

        Regards,

        MFelix