Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
@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
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,
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Proud to be a Super User!
Check out my blog: Power BI em Português
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsRegards
Miguel Félix
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
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.
Proud to be a Super User!
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.
Proud to be a Super User!
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.
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |