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...
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.

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

siddhantk989's avatar
siddhantk989
Helper III
9 years ago

MFelix,

 

   I was using the general calculation without using any formula.

 

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

 

 I am trying to implement the new solution using SUMX and I am getting a circular dependency error in it.

 

 Just to inform you Sales is not a column it is measure that i have created based on conditions.

 

Thanks,

Siddhant

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    It would probably help to know what that measure formula is. Hard to tell why a formula is failing if we can only see half of it.

    • siddhantk989's avatar
      siddhantk989
      Helper III
      9 years ago

      Anonymous

       

      th measure formula is simple. It has sales calculation based on current year.

       

      Sales = CALCULATE(SUM(Table[Actual Sales]),FILTER(Table,Table[GL Fiscal Year] = MAX(Table[GL Fiscal Year]))) 

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Since I don't see any of the column names mentioned in that measure in your sample table, I'll assume this all refers to a different table. That would mean that if you tried to run that measure in a column on this table, it would always return 0 because it has no row context to make that filter statement do anything. So you're always multiplying something by 0.

  • MFelix's avatar
    MFelix
    Super User
    9 years ago
    If.you change the sum(sales) for.your measure name.it.should work also.

    • siddhantk989's avatar
      siddhantk989
      Helper III
      9 years ago

      MFelix,

       

       Sorry i didn't get it. can you pelsae tell me again what to do in it?

       

      Thanks,

      Siddhant

      • MFelix's avatar
        MFelix
        Super User
        9 years ago
        Recalculate your.measure using the sales mesuare.

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

        In the first parameter use the table where you have your sales information.

        Are the targets in the saless table or in another separate table?

        Mfelix