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
Anonymous
Not applicable

Using FORMAT() with CALCULATE() in charts

Hello - I'm looking for a way to show a calculated measure in a bar chart. I have 1 existing measure that works fine, and the 2nd measure is the same measure but uses the SAMEPERIODLASTYEAR function. The problem is that i'm unable to get the 2nd bar to show in the chart.

 

Here's the formula i'm using:

measure1 = CALCULATE(FORMAT([Autopay Enrollment Rate],"Percent"),SAMEPERIODLASTYEAR('Calendar'[Calendar Date]))

 

I saw in another thread that using VALUE() might work. Unfortunately when I wrap the formula in VALUE, it throws an error. Any ideas?

 

that other thread for reference was https://community.powerbi.com/t5/Desktop/Format-function-destroys-some-of-charts/td-p/106221

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

I'm curious at the need for the Format here.  Once you have created the new measure, you can format it as a percentage using the Modelling tab of the ribbon.  Percent is a selectable format.  Is there a specific need to convert your value into Text with the format function?

Anonymous
Not applicable

Ross, perhaps if I share a visual it will help explain the problem. The data displays properly in a matrix-style grid, but not in a chart. I suspected the problem was to do with the formula. Maybe VALUE() has no merit here, but I can't find a solution to having the data display in a chart.


EDIT: I also should clarify that I'm connected to a data model using live connection. I cannot modify/convert the datatype using the Modeling ribbon because it is grayed out.  

 

Capture2.PNG

Anonymous
Not applicable

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.

Anonymous
Not applicable

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.

 

Capture4.PNG

 

 

 

Anonymous
Not applicable

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.

Anonymous
Not applicable

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!

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.