Forum Discussion
Custom Format String
I found a solution! For the metrics Bloomberg.EPS and Bloomberg.Revenue that come from the data source, I created measures that look like the following:
The format was reverted back to text, but I was easily able to choose the correct currency type and decimal places necessary without interrupting an "N/A" output.
To fix the % Achievement measures I had created, I adjusted the measure as follows:
Again, the formatting was converted back to text, but I was easily able to fix this as you usually would.
Can't believe such a simple solution works for this issue. I wanted to make it more complicated than it needed to be. This post helped me get to the correct output.
Thanks again to those who helped!
Hi Anonymous ,
Try the following code:
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","","N/A",Replacer.ReplaceValue,{"Value1"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","infinity","N/A",Replacer.ReplaceText,{"Value2"})
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I think this is along the right track. Allow me to be more specific than before, so hopefully we can nail this down. I have two columns (Bloomberg EPS and Bloomberg Revenue) that have null values that need to be returned as "N/A" in the visual. See the following photos.
On the top is a company that does not have data for these measures, so all values are null. On the bottom is a company that does have data for these measures. When this measure is selected for a visual with the correct period, company, and source, these values (2720.714 and 0.585) will be returned. When the first company is selected, blank values are shown. See the image below.
Next (which you can also view in the photo directly above), there are two measures (% Achievement - EPS and % Achievement - Revenue; both show as % Achievement in the visiual above) that have been calculated in Power BI as a new measure, not Power Query. So, I will not be able to edit this in Power Query. They are calculated using the two columns previously mentioned as well as general EPS and Revenue, respectively. Please see the example below of how % Achievement - EPS is calculated.
These two % Achievement measures return infinity as you can see in the matrix shown above. I believe once Bloomberg EPS and Bloomberg Revenue columns are fixed to return N/A when a value is not present, this column would also return N/A... Do you think that would be correct? If not, I may need some help figuring out how to recalculate these two % Achievement columns in Power Query rather than Power BI.
Sorry for all of the trouble with this - I really appreciate your help thus far!
I found a solution! For the metrics Bloomberg.EPS and Bloomberg.Revenue that come from the data source, I created measures that look like the following:
The format was reverted back to text, but I was easily able to choose the correct currency type and decimal places necessary without interrupting an "N/A" output.
To fix the % Achievement measures I had created, I adjusted the measure as follows:
Again, the formatting was converted back to text, but I was easily able to fix this as you usually would.
Can't believe such a simple solution works for this issue. I wanted to make it more complicated than it needed to be. This post helped me get to the correct output.
Thanks again to those who helped!