John wanted a chart with only the 80 highlighted as £80 and the rest of the axis as normal 10, 20 etc
This involved a simple Custom Number Format being applied to the Y Axis, Number Format of the Chart.
Custom Format: [=80]”£ “0;0;0;
IS THAT ALL
That Simple Custom Number format tells excel to apply at custom format of “£ “0 when the value is 80, otherwise use a normal 0 custom format.
“£ “0 means, Display a £ and a space in front of the number with no decimals
0 means, Display the number with no decimals
WHAT ELSE CAN I DO WITH CUSTOM NUMBER FORMATS AND CHARTS
This technique can be extended in a number of areas
Highlight Just a Single score
Custom Format: [=80]”£”0;;;
Highlight Two Scores
Custom Format: [Blue][=80]”%”0;[Red][=100]”P”0;
Using Wing Ding, Web Dings and Other Characters
Custom Format: [Blue][=100]”4″;[Red][=80]”8″;
and the Web Dings Font
Custom Format: [Blue][=70]”P”;[Red][=90]”O”;
and the Wing Dings 2 Font
SO HOW DO THESE WORK?
As with cells you can apply custom Number formats to any of the Charts numerical objects including the Charts Axis and Data Point Labels.
1. Select the Chart and then the Axis or Data Point Labels.
2. Right Click and select Format Axis
3. Select the Number Tab
4. Custom Format
Put the custom format in the Format Code dialog and Add
5. Close the Format Axis Dialog
CAUTION
If you setup a fancy Custom number Format as say
[Blue][=100]”4″;[Red][=80]”8″;
and then set the axis scaling as Minimum 10, Major Unit 20, the two number 80 and 100, from the Custom format will not display as Excel will skip them according to the Axis Scale.
Downloads
You can see how all the above Charts are made using the Sample Data File Attached
Version – Excel 1997/03, Excel 2007/10.
LINKS
The links below will explain the intricacies of Custom Number Formats.
http://www.ozgrid.com/Excel/excel-custom-number-formats.htm
http://www.ozgrid.com/Excel/CustomFormats.htm
http://chandoo.org/wp/2008/02/25/custom-cell-formatting-in-excel-few-tips-tricks/
The formats described above can be used as equally well with Charts Numbers as with Cell Numbers.
WHAT ARE YOUR FAVORITE CUSTOM NUMBER FORMATS ?
What are your favorite custom number formats?
Share your custom formats or ideas in the Comments below:
Hui…
For a list of my other contributions at Chandoo.org please visit: Hui…
2 Responses to “Top 10 Power BI Interview Questions & Answers”
Hello...
In Power BI I have data that includes months by name only (e.g. May, April, December...)
I need to build charts etc. but i need the months to go chronologically... not alphabetically... I cannot seem to find the fix to this.... once again, my data does NOT have an actual date attached to it (like 02/01/2023)....only month names... can i use a helper table wher i id the month names as numbers 1 thru 12? and if so, how do i manage this to work for me ?
Thank you.
~Keith
You need to setup an extra table to map each month name to a running number. A simple 12 row table like
Jan 1
Feb 2
Mar 3
..
Dec 12
Then create a relationship between this month table and your month column
Now, go to "table view" in Power BI and set the sort by column to month number for the month name column on this new table.
Finally, use the new table's month name whenever you need to refer to the month name in the visuals.
They will be chronologically arranged.