Forum Discussion
Using FORMAT() with CALCULATE() in charts
So just to clarify, after clicking on your datafield, when you go into the Modeling Tab of the ribbon, the option for "Format" is greyed out? I understand that Data Type should be greyed out, but my expectation is that the formatting options should still be available to you. Is the % button also greyed out?
I note that your other field is already being formatted as a percentage. This incidates to me that the formatting has been applied to that field.
Ok yes i do see the issue. Format changes the result into Formatted Text. Your data bars require you to have datatypes that are numerical. Once you convert your result into text, Power BI no longer recognises it as something it can draw proportionally to create those bars of appropriate length.
As you have suggested, the Value function will allow you to convert back to a number, however if you start as a number then this is a bit redundant.
Perhaps you just need a third formula that never makes the "Format" conversion, thus what you display in the Matrix and what you display in the graph can be different. The third formula could multiply or divide by 100 should you need to get your result into the appropriate scale.
I think the numbers are represented properly without multiplying or dividing by zero. Instead all I want to do is change it from decimal to percent, while still keeping its numeric value. In the screenshot below you'll see how the yellow-ish bar (the problem) is formatted as a decimal. Ideally I want it represented as a percent, like the green bar.
So just to clarify, after clicking on your datafield, when you go into the Modeling Tab of the ribbon, the option for "Format" is greyed out? I understand that Data Type should be greyed out, but my expectation is that the formatting options should still be available to you. Is the % button also greyed out?
I note that your other field is already being formatted as a percentage. This incidates to me that the formatting has been applied to that field.
Before I was only clicking on the chart, and not the measure. This time, as you suggested, I clicked the measure from the "fields" list and was able to format the chart properly.
Thank you for your help!