Forum Discussion
Format causing measure to return blank.
I can't share the file but I will work on building a comparable file that has the same data model layout and see if I can reproduce the issue.
I just tested the histogram bucketing and formatted measure in a table (rather than just a bar chart with labels) and it works fine, so now I don't know why it's not working properly with the bar chart. I believe the issue is that FORMAT() returns a text value, which the bar chart doesn't know is actually textual representations of numbers.
you're not kidding, I went round in circles on this one.
the only thing I had considered although never followed it through was to have a stacked bar graph that has two outputs one for percentage values and one for the numerical values.
depending on slicer selection would depend on which one had data in to show on the graph - I'm assuming that it would then allow you to format the measures independently.
If I give it a go I'll let you know.
Dog
Dog That's an interesting idea; let me know if it works. I wonder if the "auto" scaling for the chart is "smart" enough to switch between Percent and General (with scaling) if you do this!
Hi spotpuff
it does and doesn't :)
for me it will actual work fine as my client prefers data labels to the axis.
Data labels update accordingly and the correct format is shown for currency or percentage.
The X Axis though is not clever enough as it displays, I assume, based on the first measure passed through in the values list which in my test case is currency (regardless if there are any values or not)
Sorry
Dog
Hi Dog I tried the solution you mentioned of stacked bar chart with one measure that returns a value, and one a percent (multiplied by 0 if it isn't to be displayed). The chart works fine if the individual measures are formatted properly except for the Y-axis, which shows in percent. So I had numbers like 6,400bn% in the y-axis.
It's unfortunate that there isn't a way to dynamically format the y-axes as leaving these as "Auto" is best.
Even bar charts in the new experimental table visual don't work if you use Format(), which is, again, unfortunate.
you sure aren't kidding when you say that it is near impossible to search for solutions for this. Just came across this post after a good hour of searching. This is also one of the first posts that I have seen someone else besides myself using the SWITCH( ) statement to dynamically swap filters based off a slicer. I was just recently attempting to do the same with columns and dynamically switching them in for the Row fields with a slicer in the same way...that was impossible to find anything on, but still got it.
I just ran into this same problem. It's really not a huge deal I just do not want my users to miss the fact that the data is actually percentages. Were you able to find any sort of work around? I cannot believe it is this difficult to accomplish.
Best,
Nathan
Unfortunately I didn't find any solution. As of now I have 2 histograms, one for % and one for values. It is interesting because they do vary, so maybe it's not the worst thing.
The stacked bar chart method does work; do a stacked bar chart with 2 measures, one a % and one a value. When you use the slicer to select % or value, only the correct bar chart shows up (and the other zeroes out). The only issue there is the y-axis is either in values or %, which is unfortunate. The only way I can think of getting around that is with a custom tooltip logic, maybe.
I found somewhere to vote on this idea if you have not already https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/15231165-conditional-formatted-measures-using-switch
