Dashboards are very common business monitoring tools, but creating them in excel with all the bells and whistles is not so easy. So here is a quick 1-2-3 on how to do it.
Lets take a sample of 2 consecutive year sales figures for 7 regions. The colums have Region name, 2004-05, 2005-06 figures and finally YoY Growth percentages. The lame dashboard should look something like this:
But may be we can make it little better. Ideally, a person looking at this would like (to know) the following things:
- What are the things that are going up / down / remaining constant
- The chart should look simple and not cluttered; meaning, there cant be multiple columns to present information. He/she should be able to look at one column and concluded something
- May be little graphics wont hurt the presentation while retaining the information.
So, a cool dashboard would look something like the below one:
Well, how to get it in 3 steps?
- Type the following formula in the cell F5 and drag it to apply to all the cells
[Click on the image to see bigger version of the formula] - Select the range F5:F11, goto Format->Conditional Formatting and enter the following values there:
[Click on the image to see bigger version of the formula] - Finally, if its already not, change the font of the worksheet to Arial, (see those arrow marks, they are not available in all fonts. And btw, if you dont know how to insert them in the formula use Start->Programs->Accessories->System Tools->Character Map and then locate the symbols.)
So, go ahead and impress everyone with the cool dashboards.
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.