Exchange Rates and Power BI

Let Power BI Convert your Currency

This week, we at 1-Answer Analytics began discussions with a large attraction company that has multiple attractions in multiple countries generating revenue in multiple currencies. We were able to highlight one of the features of Power BI is the ability to convert currencies, with proper formatting, seamlessly showing a visualization that adapts to whichever currency they choose. For a company that generates millions of dollars in revenue and expenses, having a single pane of glass that shows all financials in one currency is a huge time saver.

If you have a business with branches in several countries, this is a very helpful tool. For example, if your company owned 50 McDonalds located in 12 different countries, a single dashboard that could show daily sales reports all converted to one currency. This would provide more actionable information than simply showing sales in each currency.

Properly developing the correct dashboard can be a challenge. Here are some of the steps you will need to take. Import current Exchange Rates by using a URL. One we would use is – www.xe.com. Using web data is needed, because this solution is not hardcoded but needs to be refreshable as you to grab the latest exchange rates. After Power BI imports the html charts from the website of your choice, you will need to transform the data removing any extra columns or sections that are unneeded.

You should also want the proper currency symbols and Currency Codes (three letters). The Exchange Rate table you are connected to may or may not have those symbols and codes clearly labeled. One website that can easily provide all the currency symbols and codes is www.theforeignexchange.com or another is www.justforex.com . There should be minor transforming of the data. Use the Merge Query option to bring the currency symbols into Exchange Rate table.  Depending on the Exchange Rate website you choose, the default conversion may be to the Euro. For our clients, we would change the Exchange Rate to default to the USD (U.S. Dollar).

When you are transforming the data, you may wish to select only those currencies you generate revenue in. No need to have Exchange Rates for 35 currencies if you only have operations in three countries. However, you may expand your operations, and may want those other currencies available. It is easy to have all currencies in the data and then filter under Values to only those three currencies you regularly need.

The great value is in the dynamic aspect of the data. This allows the formula to change when the exchange rate changes. To get the dynamic quality, you will need an external tool called Tabular Editor. It is one of the best ways to develop the needed calculation groups. If you are not familiar with downloading external tools, or how to ensure dynamic qualities to your data, a Power BI consultant, like those of us at 1-Answer, can be contracted to build this very quickly.

One important tip to remember, there are some currencies that use a dot (period) in the currency symbol. One example is Libya’s currency. Another is the Russian Ruble (though it is the colloquial symbol with the period and not the more formal symbol). That dot will be read as a period, and not part of the currency symbol, in the valuations Power BI creates. This will throw off your data. You will need to manually change that currency line to ignore the dot.

Another tip – You will want to reference the Country Currency Code (three letters) as well as the currency symbol. The dollar symbol “$” is used by many different currencies (Canadian, Australian, Bahamian, Cayman, etc), but each has a different Currency Code. Some Currency Codes look similar, so confirm that you have the correct currency by referencing the currency symbol as well.

As the world’s economies become more intertwined and businesses grow into foreign markets, having Power BI to provide easily understood revenues and costs transformed into one currency will provide analytic insights that successful companies need. This great feature of Power BI may not be a benefit to your company in the present, but it may be exactly what you will need in the future.

Seven Tips for Hiring a Business Intelligence Consultant

As a business owner or manager, you have decided you need to get a better grasp on all your data. You know there are ways to understand and act up the data you receive, but it is all too much and too hard to decipher. You need help.

You can have your staff receive training, and wait on the results, or you can hire a highly trained consultant in Data Analytics, or Data Management, or Power BI, or Azure, or Tableau or something else in Business Intelligence. How best to find a consultant who can quickly and efficiently give you the information from your data that will empower you to act effectively for your business. Here are SEVEN Tips for you to consider when hiring a BI Consultant.  

  1. Agree on Definitions/Terms. Communicate What You Want

    You may say “I need a dashboard with all my Sales information on it.” Sounds simple enough. But what do you consider “Sales Information”? Sure, you want information on how much you sold, but do you also want information on how much it cost to sell that? Do you want labor costs included? Advertising cost? The cost of customer acquisition? Does the cost of returns get added to your Sales figures?

When you say, “Only C-Level Executives will need this report”, do you also mean your Board of Directors? Your investors? Your owners? We once worked with a business that was family owned. Not only were all the members of the family to be emailed financial reports, but they also wanted the spouses of each family member sent the information.

  • Ask for References/Ask for Samples

    While provided references are almost always going to be positive, they can still tell you a lot about your potential consultant. You are hiring a consultant for their expertise; you want to make sure they are truly experts in the field. You want to find someone who has maintained relationships with long-term, repeat clients. Ask the references whether the consultant was available when needed and if pricing or timelines changed during the process. Ask for samples of the consultant’s previous work. Remember, all samples should be redacted to protect the former client’s confidentiality. With the samples, seek to see the competency level. Data Management is often as much art as it is science.
  • Get Everything in Writing.

    Your potential consultant should provide an agreement/contract that includes a scope of work. Feel free to ask for changes and edits to the Scope of Work. Remember that legally it is what is in writing that counts, not what verbal promises may have been made. Ask potential consultants for this agreement with a deadline. This will help you see how responsive each candidate is to your timeline. Remember, you can always change an agreement/contract, if both parties agree in writing, after the project has started. Remember, baseball great Yogi Berra once famously said, “A Verbal Contract is Not Worth The Paper it’s Written on.”  
  • Do an NDA. Require confidentiality.

    Have the consultant and any members of their team sign a Non-Disclosure Agreement. The consultant will need to see your data and get to know your data to effectively manage your data. But your data has information you do not want your competitors or maybe the public to know. You may also want them to sign a Non-Compete agreement so your consultant will not work with your direct competitors for a fixed length of time.
  • Personalities Matter.

    Hire someone you would like to work with. Sometimes it is difficult to work with a person who you just do not care for, or who rubs you the wrong way. You would be surprised how many companies simply go for the lowest bidder, without considering who they are hiring. The task of hiring a consultant should be treated with the same care as hiring a permanent employee. You need someone who can not only meet your project needs, but who also communicates well in one-on-one and group situations. Ideally, your consultant should be someone who you would be happy to work with on an on-going basis rather than as a one-shot deal.
  • The Value of Hiring Locally.

    This worldwide pandemic has shown us how we can work with others remotely. No longer do you need your consultant on your premises to manage your data. However, there are many good reasons for working with a local consultant. The consultant may better understand your needs if they see your operation in person. When many people are trying to communicate a problem(s), the clues from body language can be lost through virtual conference. Often, we are given mixed messages. In person, we know what the highest priority is and who’s wishes would outrank the others. Finally, if things do not go well, or if there has been an unacceptable delay, it is easier to get resolutions by going to the consultant’s office in person. Trying to get a refund, or company materials returned is harder from 1,000 miles than from 10 miles.
  • Look for Red Flags.

    Recently a company wanted to do business with us, but for our payment they would not accept a credit card, a company check, or a purchase order. They wanted a wire transfer and then they would start in four days. Huge Red Flag. Another issue occurs when a consultant is living hand-to-mouth. They may need your payment to cover any of their expenses in doing your work. They may not be able to purchase a needed tool/equipment/software until they receive your funds. This should be a red flag. If they demand payment upfront, realize you have no recourse if their business fails, or shutters before they complete your project. A good consultant should be happy with Net 30 billing.

With any luck, the consultant that you have selected will not only successfully improve your data management but will be a valuable asset to your company for years to come, making your job easier and your company more profitable.

About 1-Answer Data Solutions:

Founded in 2015, 1-Answer Data Solutions are Power BI experts who have developed a proprietary BI Accelerator which includes a unique De-duplication process. Experienced in many verticals, 1-Answer has served the healthcare sector with many industry specific templates. 1-Answer provides Free Consultations, Proof of Concept tests, competitive pricing, and Net 30 billing. Visit the company’s website at http://www.1-Answer.com/ or email us info@1-Answer.com

HOW CAN 1-ANSWER HELP YOU?

At 1-Answer Analytics, we can help your business grow through data management and the Microsoft tool POWER BI. There are many ways our skills can help you gain more insight from your data. Here are three specific ways:

A) POWER BI EXPERTS – Do you need consultants who can develop easy to understand dashboards for you to gain actionable insight? 1-Answer can build the custom dashboards you need through our consulting services.

Low, Competitive Rates – Faster Development – Customer Friendly

B) DATA LAKE – In this Information Age, sometimes the amount of data can be unwieldy and unmanageable. If you have too much data and not enough insight, let 1-Answer develop a Data Lake to retain all of your different data streams written in different formats.

Your Information Connected – You Gain Insights to Take Action

C) DE-DUPLICATION – If your business is blessed to have tens of thousands of customers, you will have duplicate information in your data banks. When you need to know which John Smith needs what and that you have all the information on that John Smith, you know your records are accurate. 1-Answer can quickly and effectively diminish your data duplicates.

Accurate Client Information – Security Compliant – Quick and Cost Effective

Erase Your Bias – Use Data Analytics to Increase Your Sales.

Use Data Analytics not Bias
Use Data Analytics not Bias
Are your personal Bias costing you sales?

How Can Data Analytics Increase Your Sales? All businesses want to increase their sales, that is why we have Sales Managers to manage and coach our Sales Representatives (Salespeople, Account Executives, Business Development, etc). Previously, Sales Managers would train/coach/mentor their Representatives to increase company sales. But where is best to allocate your time and resources?

“Johnny has the lowest sales last month, I need to spend more time with him,” said many a Manager. “Suzy is doing great. No need to spend anytime with her.” These views are presumptions based on only one metric, overall sales. Sometimes, a manager spends time with Representative who is not the one in greatest need of coaching, but based on the Representative’s personality, “that person is the easiest to work with.” These are some of the bias managers bring to responsibilities in coaching.

Recently, I attended a presentation by Doug Johnson with CoPilot Sales Coaching where he discussed 3 Bias that affect effective sales techniques. He cited the book Moneyball by Michael Lewis and the research of two Israeli Data Scientists for his presentation. The three bias, Doug educated us on were:

  1. Information Bias – Making assumptions on limited information and assuming that is all you need.
  2. Performance Bias – Using a history you remember of a person’s past performance to assume those attributes are still present.
  3. Forecast Bias – You are expecting certain outcomes and then are not surprised when those are the outcomes you see.

When Sales Managers are looking to take action, they may ask “Which of my Sales Representatives need the most coaching?” Their answer should not be based on personal judgement (bias), or the employee’s request (their bias) but based on the empirical data. There are many metrics to understand: Who has sold the most? Who has had the greatest number of deals? Whose deals are the most profitable for the company? What is the ratio of deals compared to hours worked? Are there regional differences based on weather, seasons, natural events? Who is the most improved and tracking upward? Who, regardless of actual sales, is tracking downward? Who used administrative help to increase their sales?

When your Sales Data is properly managed, you can have a Sales Dashboard in MS Power BI that can visually answer all these questions and more. Your initial dashboard may just give the immediate information that is most pertinent. But each graph and chart can be drilled down to greater specifics. You will be able to easily compare and contrast Representatives, areas, products, and labor in seconds. There is a basic AI in Power BI that can do predictive analytics based on the data. It can predict future trends based on the past data.

This data can not only show who needs coaching, but it can also be used to see when they need it. Where in the Sales Cycles are they running into problems? When we look at the data on the whole sales cycle and can drill down to each Representatives, we can glean information on where in the process there a problem and coach towards that issue is.

One problem many Sales Coaches have is in training a Representative who already believes they are successful and their ways work. When you try to train that confident sales persuader, they will try to convince you they don’t need coaching and will blame your bias for trying to help them. So how can you persuade them that they need coaching? When it is based solely on empirical data, there are no hurt feelings. You are sharing data on where they might need assistance. It is not your opinion; it is evidenced by the research that Power BI did for you.

When you base your training schedule on your perceptions, your bias may not lead you to the best use of your time or the highest ROI on your coaching.

Drilling down on the data of your sales and your sales team, you may find areas of lost opportunities. The time to push new products and services, and to increase upsells can be found in your data analytics. Developing Power BI dashboards to do analytics will take time and money. However, the insight provided can dramatically increase sales, allocate training time properly, and reduce wasted efforts. The increase in revenue and the decrease in expenditure make the investment in properly using your Data Analytics a profitable step for your company.

Don’t let your presumptions, your bias against another new software program prevent you from realizing the opportunities of greater sales, greater time management, and lower expenses. Bias can cause us all to make big mistakes. First, we need to acknowledge the bias we bring into our businesses. Overcome your bias and increase your sales. 

SEVEN KEYS FOR BUILDING A BETTER POWER BI DASHBOARD

Building dashboards

You are designing Power BI dashboards for your company. Here are some ideas to keep in mind –

1 – Get feedback from the user(s) of the dashboard.

                It can be so frustrating when you create something that you think is beautiful and provides all the information a person could want, only to get a lackadaisical reaction from the planned user. Or worse, nothing but negative comments. It is important to get feedback AS you are building to have your work used in ways that will benefit the company and you.

2 – Map your Dashboard designs.

                Every visualization should answer a question. A person could put together some graphs, but it won’t tell a story. You want to design dashboards with actionable insights, where the visuals and placement tell a story and lead the user to know what action is needed. Normally, the question is the title (the top) of each visualization. Next, major metrics on top row are answering the question. The middle area should expand the answer. At the bottom (or in separate tabs) are where the actions are needed to solve the question. Layout adds to the story. It is not random.

3 – Personalize dashboards when you can.

                Who are you building a dashboard for? Even if it is for yourself, add touches that make the visualization, titles, and color scheme something that the User is comfortable with. Personalizing it with their favorite color schemes, background, fonts, style of graphs will make your product more enjoyable for the User and therefore more likely to be used.

4 – Gain Inspiration from Others.

                When you are visiting competitors’ sites, industry leaders, and other verticals, if you see elements they have incorporated into their dashboards, feel free to figure out how to add those elements to your design. Remember to always get feedback from your End User that these new design elements are something they would want.

5 – Be careful not to over design.

                So many of the images, gifs, and links we can add to the visualizations of a dashboard are impressive. But remember the purpose of a dashboard is to provide actionable insight for the User. Make your effective dashboard into a work of art. Don’t try to make your work of art into an effective dashboard.

6 – With your design, keep Mobile Power BI in mind

                With the need to access key information 24/7/365, many executives are adding Power BI to their smartphones and tablets. This great access, but as a designer you need to realize that the varying sizes of phones and tablets, portrait and landscape viewing will alter your design. A smart dashboard designer builds their work so it will adapt to format and size differences.

7 – You can use large Fonts for Key Information

                Often executives check their dashboards looking for key numbers (sales data, profit margins, numbers of accidents, product shipped, etc.). While all the additional information is additionally important, the main numbers your User is looking for should be in large fonts, easy to read with the first glance. Don’t bury the lead in the needed content. Shout what needs to be shouted.

A Brief History of Business Intelligence

Today many data analysts and business leaders use the abbreviation BI to represent Business Intelligence. But what is its origins? And why would we want to know?

The term Business Intelligence came to widespread use in the late 1990s. It is often credited to Howard Dresner (later a Gartner analyst), who in 1989 used the term to describe “concepts and methods to improve business decision making by using fact-based support systems.” Today, the definition of BI can mean so much more.

The earliest known printed use of the term Business Intelligence is from 1865, in Richard Millar Devens’ Cyclopædia of Commercial and Business Anecdotes book. The term is used to describe how, a 17th Century banker in England, Sir Henry Furnese received and used information to increase his profit, in a manner that was quicker than his competitors.

Sir Furnese used a system of informants to learn the status of battles in Holland, France and Germany. He knew that the outcome of these battles would affect England’s economics. His allies provided him the information before any of his competitors or the public learned the results.

In a 1958 article, Hans Peter Luhn, a researcher at IBM, used the term Business Intelligence to represent the interrelationships between facts (information) often gleaned from computer data and actions business take to achieve a desired goal (often profit).

In the 1960s, businesses started to use DSS (decision support systems), which are computer-aided models created to assist in decision making and planning for achieving business goals. As computers became more prevalent in businesses, they also became more important in making business decisions regarding planning and forecasts. The DSS model continued strongly throughout the 1980s. BI has evolved from DSS.

The explosion of personal computer usage throughout the 1980s and 1990s, created a “Big Data” surge. Business leaders now had more data about their business, their customers, their competitors, and market forces than ever before. For many businesses it was too much data. Too much to process, too much to enter, too much to analyze. Data was ignored because it was too difficult to access all of it. Or businesses fell into “paralysis by analysis” and could not act swiftly on the information they received.

By the 2000s, efforts were made to have collect and store the surge of data in more efficient ways. Once data was stored, the next effort was to transform data from a myriad of sources into a uniform language, so it could be comparatively analyzed. This becomes the Business Intelligence that is still evolving today.

Huge amounts of data are extracted from multiple data sources, transformed, and loaded into reports, dashboards, and other visualizations to empower business leaders to make wise decisions based on facts. This is the present-day world of BI.

BI has gone from informants on battle fields in the 1600s to data lakes that are transformed into computer dashboards giving you real-time pie-charts of expenditures and profit. When we know our history, it can help us to predict the future.