Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
siddhantk989
Helper III
Helper III

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.

 

Capture.PNG

2 ACCEPTED SOLUTIONS

@MFelix and @KHorseman 

 

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

View solution in original post

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

View solution in original post

17 REPLIES 17
MFelix
Super User
Super User

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



@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

 

If.you change the sum(sales) for.your measure name.it.should work also.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



@MFelix,

 

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

 

Thanks,

Siddhant

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

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



@MFelix I tried this but I am getting blank values in the Measure as well.

 

@KHorseman i can not shar the sample of the data set because i have around 92 attributes in my data set.

@MFelix and @KHorseman 

 

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

Hi @siddhantk989,

Glad to hear you have resoled your issue by yourself, please mark coresponding reply as answer. So that more people will benefit from it, and find workaround easily.

Best Regards,
Angelia

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

Hi @siddhantk989,

Great job. Please mark your solution as workaround, so that more people will benefit from here.

Thanks,
Angelia

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@KHorseman

 

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]))) 

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@KHorseman,

 

 No I have jsut 1 table. They all are part of same table. What I am showing in the sample table is the table vusal that I am trying to create from my data set. IThe measure and column are part of the same table.

Can you show a sample table that actually shows all the columns involved? Your sample table in your first post doesn't include any of the columns used by the measure so I can't tell how the measure is affecting the results. If all these columns are in this table we need to see all of them.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.