Forecasting with PowerBI… really?

So PowerBI just added a Forecasting feature? As a Forecasting specialist, I got really interested in this new feature.

What all organizations are looking for is not only a tool to explore the past, but also to get insight about the future. No wonder it’s a great marketing tool for all BI platforms to come with a Forecasting tool that can provide insights from your data about predicting the future.

This feature will allow you to build sleek charts like this one, but how good is it really? Can we trust PowerBI for forecasting future trends?

8ec980b1-822c-4f72-bde7-13944329d088

How it works?

First let’s take a look at what we have. Follow this link to understand how to activate the PowerBI Forecasting feature. Now you can have PowerBI forecasting a Time Serie for you. Great but wouldn’t it be nice to understand HOW PowerBI forecasts it? What is the model used in background?

The PowerBI team answered this question in this post where they explained that the algorithm was the R ETS(A,A,A) or ETS(A,A,N), which is an Exponential Smoothing algorithm that you can choose to make Seasonal or not (seasonality will be calculated in an Additive not a Multiplicative way). I will refer you to this paper for more information about R and this ETS algorithm.

Back to basics: Forecasting 101

We will dedicate some more posts on Forecasting Time Series in this blog, so stay tuned!

What you should know in the meantime is that if you want to get a good forecast you pretty much need to go through all the following operations:

  1. Clean your data set from outliers:  you might want to remove exceptional values (think zero sales because you were out of stock, or an exceptional sale due to a promotion for example) from the baseline as theses values are not relevant and risk of getting your forecasting algorithm off-track
  2. Pick a forecasting algorithm and the values of its parameters: there are a bunch of methods you can use to forecast a Time Series: Time Series Decomposition, Moving Averages, Exponential Smoothing, Holt-Winters, ARIMA, Croston… to name a few. Each method comes with some parameters values that have to be defined and this is equally important. It’s much better to use a simple method with relevant parameters than a sophisticated algorithm with an absurd set of parameters.

How to best clean your data, pick the best algorithm and set of parameters for it are important and difficult questions that Forecasters have to answer. There is often no absolute answer and the algorithm that works best today will not necessarily be the best one tomorrow. So should you change every time you forecast or should you give more points to stability? Should you let the algorithm pick himself the parameters to use or should you fix them based on your knowledge of the business? Again important questions!

Actually these questions are so important that we touch here the core of what Forecasting really is: STRATEGIC! Forecasts drive Strategic decisions and Tactics.

As such Forecasts are usually reviewed at the highest possible level in Organizations. And the most important thing when presenting a forecast to a top executive is to be able to JUSTIFY how you built it: does it makes sense based on past trends and seasonality? The worst possible answer to justify your forecast is: “the machine did it”…

PowerBI Forecasting

The problem of PowerBI’s Forecasting module (as we are writing these lines), is that it answers all these strategic questions for you and leaves you with very little FLEXIBILITY to chose methods and parameters

  • data cleaning: PowerBI won’t really do it. At most it will perform “Linear extrapolation” between missing data points
  • you can pick if your algorithm should be Seasonal or not (thank you!), but the way to calculate seasonality will be Additive and nothing else (sorry but I actually usually prefer to consider Seasonality as Multiplicative…)
  • The algorithm will be the R ETS additive one, with linear trends and nothing else. If you want to use a different method or non-linear trends you can forget it
  • Parameters of the ETS are automatically picked (this is usually done by doing back testing and minimizing the error for the 1st time period forecasted). This can be quite dangerous because it’s not so hard to be accurate for the 1st time period, many methods and parameters can provide good forecasts for that. But what Executives and Top Management will likely look at is more the medium to long term forecast, and the parameter that minimizes the first month error is not necessarily the one that will provide the best medium to long term forecast.

Don’t get me wrong the R ETS algorithm is quite good and will provide excellent results in many cases. But I also know a lot of cases on which it is not appropriate, will give you absurd forecasts and you will be in difficulty when having to explain them to your Management. So for all these reasons it’s important to understand how this Forecasting PowerBI “Black Box” works, what it can do and can not do, and most importantly be careful and not trust it blindly like “PowerBI is so smart and tells me this so it must be true…”

Bottom Line: 

  • It’s great that PowerBI works on Forecasting features (we insist that these are still in beta version as we write and will be for sure improved in the near future). We are also happy to see that they use R which comes with awesome packages for Time Series analysis and forecasting, so this also goes in the right direction
  • However keep in mind that Forecasting is a highly strategic activity and being able to justify how you calculate your forecast is as important as the forecast itself. Therefore a little bit more control in the choice of the algorithms and parameters would be highly appreciated instead of having a “Black Box”. Why not also provide very simple “What if?” tools such as boxes for Forecasters to build a forecast based on simple strategic inputs from their management like Trend: +10%, Seasonality: 12 months… Come on PowerBI team, you know how much we love you on this blog so… challenge accepted?

AnalystMaster

Too big for Excel…

We have seen in our PowerBI Best Practice Article that it is best to work with data at maximum level of detail. Well the problem is that sometimes it can be a lot of data… like really a lot…

One day I wanted to extract a lot of information from an ERP system. Not only the data was way above 1m rows Excel capacity, but I was also limited by how much information I could extract from this ERP at at time.

Crazy isn’t it? You know the information you need is out there but it’s LOCKED… What is the point of owning a Ferrari if you’re only going to drive it in first gear?

So what can you do? If you have read this article, you know that PowerBI is the right tool for you. We will show you how to use it depending on how much resources you may have or support from our IT department.

Regarding your data, your have essentially 3 options:

  • Use PowerBI to connect directly to the database
  • Make it fit
  • Work around it

Use PowerBI to connect directly to the database

Good news #1: we know a very cool tool: PowerBI who is capable of handling large amount of data, this is the way to go!

Good news #2: it is wired with tons of connectors to big databases (SAP, BW, Hadoop…)

If you get sufficient support from your IT department you should be able to activate these connectors and work without any issue with your large dataset

However if you are reading this blog, we do not expect you to get this kind of resources or too much support from your IT department. Your are left with the last 2 options:

Make it fit

At the other end of the spectrum, you can obviously try to make your data fit by reducing the amount of information you bring into PowerBI. This can essentially be done by

  • filtering: by filtering out some of your data, based on some attributes or combinations of attributes that you are less likely to need later you can make your dataset smaller and eventually make it fit within your capacity requirements. You can also limit your time horizon
  • aggregating: by giving up strategically on some levels of details that will be less likely to be useful in your analysis later on. For example instead of brining data at a daily or weekly level of detail you might consider monthly.

In our opinion, the Make It Fit option should be activated only in last resort. If you go for it, since you will obviously lose some detail in your information, be strategic about it. Think wisely about which levels of details are less likely to add value to your analysis and take decision on which data to leave out accordingly

Thankfully even with limited IT resources, we do not take NO for an answer and there is a last option:

Work around it

This sounds absurd: we don’t have the tools to manage (extract or store) our data with maximum level of detail, but we don’t give hope that we will import it into PowerBI anyway. How? by slicing it and re-aggregating it later!

  • Slicing: First you need to slice your data into slices that fit within your extraction capacities. For this you can refer to the filtering strategy of the “Make it Fit” section.
    Define some filters so that your extraction is manageable and run them one after the other so that you get the full data set.
    Obviously the less slices the better, so don’t necessarily limit yourself with the storage capacities of Excel or Access. Just focus on your extractions capacities and Go with Text (.TXT) files for Storage if necessary
  • Re-aggregating: this can be managed with PowerBI using the Append Query function in the Query Editor
    This works seamlessly if you have a few slices to re-aggregate however if you have many you might want to run a batch to manage this aggregation outside PowerBI

Back to my ERP extraction problem, unfortunately there was no direct connector for PowerBI that I could use so I got the IT department to extract the past information for me into a giant text file, and to run a script every day to extract the new daily data and drop it into another TXT file. All TXT files were in a folder that I could access.

I was left with a bunch of TXT files with the same format, the only thing I had to do is aggregate them together. A quick Python script later (that I’m giving to you below) and I was done. I managed to get all my data with maximum detail out of this ERP and play with it in PowerBI!

Never give up!

AnalystMaster

Appendix: how to aggregate text files using Python

Text files are not limited in row capacity and could be a efficient low cost way to store large datasets.

One of the best tool to work with text files is Python.

If you do not have Python you can download it for free from the Official Python website

Here we suppose that:

  • you have a file called Startfile.txt at the path Path/Startfile.txt which contains the headers (and only the headers) of all your text files (which are all the same and in the same order)
  • that all the text files that you want to append together are in a folder called Path/Files ; and that they are the only text files in this folder
  • you have a file called Finalfile.txt that will contain the results of your appending of all the text files containing your data. This file is at the path Path/Finalfile.txt

What you need to do:

  1. open your notepad and copy paste the script below
  2. replace Path by the actual path where your files will be stored (and only these files), create the Startfile and drop your other text files in the Path/Files folder
  3. save this script as a .py extension.
  4. Run it, that’s it! You can now open your Finalfile.txt or connect it to PowerBI to read your data

import glob

g = open(‘Path/Finalfile.txt’,’w’)

g.truncate

s = open(‘Path/Startfile.txt’,’r’)

g.write(s.read())

g.close()

g =open(‘Path/Finalfile.txt’,’a’)

path= ‘Path/Files/*.txt’

file=glob.glob(path)

for fle in file:

with open(fle) as f:

text = f.read().splitlines(True)

g.writelines(text[1:])

g.close()

Compare Time periods using a Date Table in PowerBI

Use a Time Master Data

In my previous blog post about PowerBI Best Practices, I insisted on the importance to separate your Measures from your Attributes and to store Attributes in Master Data tables.

This allows you to slice your data on several tables and therefore on several visuals at the same time, which is what you very much want to do when exploring data with PowerBI.

When we speak about Attributes in a Master Data table, we usually speak about Products, about Stores, but we often forget an important dimension: TIME!

In fact each Time Period could be the Key to a Master Data table and come with Attributes as well. We recommend you build such a Date MasterData table and connect it to all the tables with your measures. Here are some examples on what you can do with this:

Example 1: YTD comparison

Let’s say we are in October 2017 and we want to compare YTD (Year To Date) Sales meaning the Sales of January 2017 to September 2017 vs. the same period last year (Jan 2016 to Sept 2016). That is a very common problem, so how can we handle it with PowerBI?

One of the top solutions is actually to create a Time Master Data table which will take each date as a Key and, with a YTD Index which will take “YTD N” as value for the dates in the YTD of the current year (Jan 2017 to Sept 2017) and “YTD N-1” as value for the dates in the YTD of the year before (Jan 2016 to Sept 2016).

Then you can compare your Sales YTD 2017 vs. 2016 building the following measures:

Sales_YTD_N = CALCULATE (SUM (Sales), MonthyMaster[YTDIndex] = “YTD N” )

Sales_YTD_N_1 = CALCULATE (SUM (Sales), MonthyMaster[YTDIndex] = “YTD N-1” )

YTDSales_% = [Sales_YTD_N] / [Sales_YTD_N_1] – 1

Example 2: Comparing YTD monthly trends on a Line Chart

Let’s say we would like to build the following Line Chart for our Sales, with the month as X-axis and the Sales per Year as Y-Axis

Capture d_écran 2017-10-30 à 20.16.57

Again the Monthly Master data could come handy, if we build some columns Year and Month as dates attributes in the Monthly MasterData

Other Examples

You can also use the last Closed month Index, the current month index, Quarters, Half-Years, rolling indexes and so on…

One I find really useful is the Relative Month index. It works as follow:

  • 0 for Current month
  • 1 for next month, 2 for Month N+2 etc…
  • -1 for last past month, -2 for 2 months ago

This is quite useful for building rolling indexes. Let’s say you want to calculate your sales for the last 12 months. You can do this with PowerBI using the following DAX formula

Sales_Last12m = CALCULATE (SUM(Sales), MonthlyMaster[RelativeMonth]<=-1, MonthlyMaster[RelativeMonth]>=-12 )

OK, you convinced me to user a Time Master Data, now how do I get one?

There are many mays to build Time indexes, especially using M language in the Query Editor

I think this is by far the best option for many reasons:

  • a date Master Data easily fits in an Excel or CSV file
  • it’s not report dependent. You can use the same table for many reports so why not build it once for all and import it in 2 clicks in every Report you will use?

I will give you the file I use for Monthly MasterData, it provides the following:

  • YTD Indexes
  • YTG Indexes
  • Last Month Indexes
  • Current Month indexes
  • Month
  • Year
  • Next and Previous 3, 6, 12, 18 months rolling
  • Relative Month which gives the relative position of other months compared to the current month

To update all indexes dynamically, you need to each month: open the file, click on the Button to run the VBA macro which will update all the values of the attributes dynamically. That’s done. Save, go to PowerBI and just Refresh the file

MASTER_DATA_DATE.xlsm

And you, how do you handle your time analysis?

AnalystMaster

 

Convert Matrix into Flat File format ( Reverse Pivot Table)

We have seen in this article the importance of working with Flat File. This is especially true for working with PowerBI but not only

Reminder: what is a Matrix and what is a Flat File format

Matrix Format

Date 1 Date 2
Product 1 1000 1100
Product 2 500 450

Flat File Format

Products Dates Sales
Product 1 Date 1 1000
Product 1 Date 2 1100
Product 2 Date 1 500
Product 2 Date 2 450

Converting from Matrix to Flat File

We have seen that converting from Flat File to a Matrix is generally seemless. Excel Users know how to do it well using Pivot Tables for example.

However the opposite is not straightforward (at least used to be…). This is annoying since the Matrix format is one of the most used by Excel users and I have no doubt you will receive most of your data in this format.

So how do make this conversion from Matrix to Flat File? Here are some options:

  • With PowerBI: We have told you you need to stop Excel and switch to PowerBI a long time ago. PowerBI’s Query Editor handles this conversion just fine:Just import your data and select ALL YOUR ATTRIBUTES columns (i.e the ones you do NOT want to “unflip”), then right click (your mouse cursor needs to be on the header of one of the attributes) and select Unpivot Other Columns
    BOOOM! Job done!

Before

After.png

  • With Excel 2016 / PowerQuery: Similary Excel 2016 comes with PowerPivot and PowerQuery built in. You just have to follow these steps to convert your Matrix into a Flat file, it’s very similar to the PowerBI technique
  • With old versions of Excel:  this post will show you how to do it

Enjoy!

AnalystMaster

 

 

The 3 rules of a good PowerBI model


 

So that’s it you have downloaded PowerBI Desktop and are now ready to start your first PowerBI report. Congratulations!

Like any architect you need to get the foundations of your PowerBI report right.

There is a lot of training material on the official PowerBI Training page to learn about PowerBI to get you started, however we’d like to give you some Key Best Practices to build PowerBI reports.

Rule #1: Import data with maximum level of detail

One day the CEO of my company asked me how a new product we just launched was doing. I extracted the Sales Worldwide vs. the Global Forecast we had, made a simple comparison and handed it over to him. He got his answer: the launch was doing below expectations, and then fired me with some more questions: “can I get the performance by market?” I went back to my desk and re-extracted the data with markets as a level of detail, re-did the analysis and handed it over to him. I thought that was the end, until one minute later he asked: “how about the performance by color? and I would also like to know which color is doing well in which market!”

I should have known better, and be prepared for these natural questions. Now imagine that instead of going back to my desk at each question and coming back to him, I had extracted all the data directly with maximum level of detail (by market and by color). I could have answered his questions straight away, and not only it would have saved both of us time, it would have had blown his mind!

Be smarter than me and make sure you always extract your data with maximum detail before you import it into PowerBI.

After all PowerBI is a true BI tool, you are not limited by the amount of data you can put in it so why limit yourself by importing data already aggregated? Import it with maximum detail (unaggregated) and let PowerBI aggregate it for you to let you explore all the levels of details you need.

So take the time to prepare your data to make sure you will import it with the maximum level of detail available to you, this will prove a valuable investment!

Rule #2: Embrace the Flat File Format!

PowerBI works best with “flat files” tables as an input. This can be destabilizing for most Excel Users who are comfortable with the “matrix” format in Excel. First let’s clarify what is a Matrix and a Flat File on a simple example of Sales by Product and by Date:

Matrix Format

Date 1 Date 2
Product 1 1000 1100
Product 2 500 450

Flat File Format

Products Dates Sales
Product 1 Date 1 1000
Product 1 Date 2 1100
Product 2 Date 1 500
Product 2 Date 2 450

Before uploading your Data in PowerBI, make sure that all your data is in Flat File Format. If you have it in Matrix format, you might need to convert it to Flat File before uploading it into PowerBI.

Why? Because you will see it’s much better to have “1 column per type information”. In the Matrix format above you have the same information (Sales) in several columns. The only difference between the columns is the date. With PowerBI it’s much more efficient to have everything in 1 column and if you need to access different dates you’ll use the famous CALCULATE + SUM formula (equivalent of SUMIF for our Excel fans)

Sales_ProductX_MonthX = CALCULATE ( SUM (Sales[Sales], Product = “Product X”, Date = Date X )

Whereas going from Flat File to Matrix format is easy with Excel (just do a Pivot Table from the Flat File), going from Matrix to Flat File is surprisingly not straightforward, and we will cover this topic in a dedicated post.

Rule #3: Separate Measures and Attributes

So now you have imported some flat files. You therefore have a bunch of tables, with a bunch of columns (fields) in them. It’s important to make the distinction between the three types of columns (fields) you can have: Keys, Measures and Attributes

  • Keys (or ID): this is what identifies a record and make it unique. For example if you have a table with Sales by Product and Date, Product and Date are the Keys because the combination of a Product and a Date is what defines a unique record. Sales will be a measure.
  • Measures (or KPI): a measure is essentially a field that can be “measured”, so usually with a numerical data type. For example Sales, Orders, Forecasts, Budget… would all be “measurable” fields. PowerBI usually automatically detect them and display they with a little Sigma sign next to their description
  • Attributes: All other fields non measurable are called Attributes. For example it could be the Product Line, Size, Color, the Country or Geographical Area of a Store.

OK easy, so why am I bothering you with this? You will see that it’s best to keep your Measures and Attributes separated as much as possible. That means you should have only Key + Measures tables (KM), and Keys + Attributes (KA) tables:

  • Key+Measures tables (KM tables): Do not import Attributes with Measures. We recommend that you avoid mixing them with attributes in the same table. Just prepare a table with the Keys identifying your record (Product, Date, Store code…for example) and the measures associated to this Key (Sales for example). Avoid polluting this table with attributes such as Product Line, Store Country etc… and import them separately
  • Key+Attributes tables (KA tables or “Master Data”): We recommend that you store your Attributes separately in what we will call Master Data tables, and that you connect in the Data Model the Keys of the KM and KA tables together

Let’s take a simple example where we want to analyze our Sales and Stock by Product Line and Country:

Poor Data Model: Attributes mixed with Measures

Capture d_écran 2017-10-29 à 14.53.45

So here you see that you have the Prod. Line and Country information in both Sales and Stock tables.

Nothing wrong at this point, but if you are going to build your model in that way, it is very important to be clear on what PowerBI can and can not do.

What PowerBI can do:

  • breakdown your Sales by Prod. Line and Country using the Prod. Line and Country attributes from the Sales table

Sales_OK

  • breakdown your Stock by Prod. Line and Country using the Prod. Line and Country attributes from the Stock table

Stock_OK

What PowerBI can NOT do:

  • breakdown your Sales by Prod. Line and Country using the Prod. Line and Country attributes from the Stock table. If you try to do so the same value will be returned for all Prod. Lines or Countries because PowerBI will have no clue how to break it down

Sales_KO

  • breakdown your Stock by Prod. Line and County using the Prod. Line and Country attributes from the Sales table. Same here, the value returned is the sum

Stock_KO

What does THAT mean? That means that as long as you want to work with either Sales or Stock information INDEPENDENTLY you are totally fine:

  • if you want to look at Sales and break them down by Prod. Line and Country, use the Prod. Line and Country attributes from the Sales table
  • vice versa if you want to look at Stock and break it down by Prod. Line and Country use the Prod. Line and Country attributes from the Stock table

But that’s quite limited don’t you think? What if you want to COMBINE Sales and Stock info by Prod. Line and Country, for example to calculate Stock Coverage by Prod. Line and Country with the below measure:

StockCoverage = SUM (Stock[Stock]) / SUM(Sales[Sales])

This measure will return a correct result if we do not try to break it down by Prod. Line or Country. PowerBI will just do the sum of Stock and Sales in both tables and divide both.

However if you try to break this measure down by Prod. Line or Country, PowerBI will only break the Stock by Prod. Line and the Sales by Country. When breaking down the Stock by Country and the Sales by Prod. Line he will use the total Stock and total Sales and this is totally wrong.

You have to be careful here, because PowerBI will return you something which could “look right” at first sight, but which is “actually wrong”:

StockCoverageWrong

So how do we get it right? By storing the attributes separately in a Master Data table and liking the Master Data to the Measures tables

Great Data Model: Attributes separated in Master Data tables

GoodModel

So we’ve separated our Attributes here, and we make a link in the Data Model between the Keys of both the Measures tables (Sales and Stock) and the Master Data tables (Product Master Data and Store Master Data)

Now thanks to the link we made between the KM and KA tables, PowerBI knows how to breakdown both the Sales and Stock by Prod. Lines and Country, using the attributes from the Master Data tables. You can now use your Stock Coverage Measure safely, and the below breakdown is correct.

CoverageCorrect

By using this technique you will be able to use PowerBI not only to explore your Sales and Stock data INDEPENDENTLY but to actually CROSS information between the two. WOW!

Bonus rules: how to build your Master Data tables

Now you know that for making the most of PowerBI you are going to have to maintain some Master Data tables to store all your attributes. Here are a few rules for these:

  • MasterData Rule #1: Have 1 Master Data table per Key in your KM tables.
  • MasterData Rule #2: Have all the possible Keys in the Master Data. In our example above, that means that each single Product need to be in the Product Master Data and each single Store in the Store Master Data. If you miss some Products or some Stores, then PowerBI will not be able to calculate on them.
  • MasterData Rule #3: No Duplicate in the Master Data tables. You need to have 1 unique record per Key. Otherwise the link will not work in your Data Model.

Conclusion of this long post: if you build your models using data with Maximum detail, import your data as Flat File, and Separate your Measures from your Attributes then link KM and KA tables together you will enjoy the full PowerBI experience and avoid many mistakes. We will come back in future posts on other side advantages of using these 3 rules.

And you, do you have any other Best Practice to share when building PowerBI models?

AnalystMaster

 

 

 

 

Forget Excel – Meet PowerBI

The tool that will kill Excel… and change your life!

In my analyst life, there was a before and an after I discover PowerBI

Before with Excel: My go to tool was generally Excel. It’s a very powerful tool for Reporting and Ad-Hoc analysis, however it has a:

  • lack of connectivity: despite some basic connectors (Access…), data rarely comes to Excel by itself for analysis. You generally have to extract it manually from your systems (like SAP…), copy paste it manually, or VLOOKUP it which is a hassle
  • lack of capacity: Although an Excel spreadsheet fits 1m rows which is enough for most users, this sounds prehistoric at the age of Big Data.
  • lack of scalability:  analysis and reports are rarely carved in stone, you constantly want to add on to it: new features, new drill downs, new information that you did not think you needed before… although this is not the weakest point of Excel, it can be quite a hassle to make a big Excel report evolve without having to destroy and rebuild big part of it
  • lack of sharing options and governance: when an Excel analysis is done, how do you share it? Usually you will attach it and send it by email, or drop it on a shared drive for people to access. Again in 2017 that sounds a little outdated… but what if you would like to share only some part of the data to some users and some other part to other users? (for example you are have a global role and did a global analysis but you would like to share the US results with the US team only and the Asia ones with the Asia team only). Are you going to build 2 separate Excel files (hard coded I mean because they would easily access the global data if you just provide them with a Filter / Slicer)?

Don’t get me wrong, Excel is still an awesome tool and I still use it a great deal. The great news about PowerBI is that is that despite my provocative title it’s totally Excel compatible. As a matter of fact it is a Microsoft tool which started as an Excel add-in (for those who would remember PowerPivot) and evolved into a stand-alone BI tool

After with PowerBI:  you will:

  • connect to your data source seamlessly: there are plenty of connectors (Excel, Access, SAP, Hadoop…) available which enable you to be plugged directly on the data source without having to extract or copy paste it manually
  • transform your data seamlessly: PowerBI comes with a built in ETL (Extract-Transform Load) tool which allows to transform and prepare your data to be used, but also to memorize each step of the transformation for next time
  • manage lot of data: there is no capacity limit to the number of rows per table PowerBI can process, so it works well on very large databases
  • scale easily: the data modeling tool in PowerBI will allow you to scale up easily if you need to add more information to your initial model. It’s very easy to add tables, connect them together, and then add a few more calculations
  • build great visuals: charts and graphics are worth 1,000 words and great analysts know that it is important to have beautiful visual outputs to present your data. PowerBI has lots of it: line chart, bar charts, funnels, combo charts, pie / donut chart, maps with geographic location management… you can hardly do better. And when you think you’ve exhausted all options you can go online to the PowerBI visual gallery and download cool custom visuals developed by the PowerBI community
  • explore with dynamic drill down: This feature exists in Excel with regular Pivot Tables, however you will find out that PowerBI takes it to a whole new level
  • share and govern like a pro: with PowerBI you can share data online and users can access it online as well or via apps on their smartphone or tablet. when sharing you can define profiles and assign each user to a profile. Governance you said? Sorted!
  • have fun! Whereas building an Excel report can be quite boring, you’re likely to discover that doing the same thing with PowerBI is fun, playful and exciting! No doubt you will wow your coworkers, boss and IT department when they see you displaying your slick reports on your smartphone!

 

Or because pictures speak 1,000 words that’s how my reports looked

Before with Excel

monthly-operational-budget-report

After with PowerBI

8507.LiveReportTile

OK Excel is so 2000… but why not use a different BI tool?

If you have access to them (because they are not free…), there are a great deal of BI tools available today (Tableau, Qlik… to name a few), which if you master them will give you equivalent functionalities as PowerBI. You are more than welcome to use them especially if your company or organization provides them, however as we are supporting the masses of analysts out there who are left out with only Excel at their disposal, we will cover mostly PowerBI in this blog for the following reasons:

  • It’s free! The Desktop version of PowerBI can be downloaded for free from the PowerBI Desktop download page. And if you want to use the cloud service to publish your reports or for other user to consume the content you will publish out there, you can get Free licenses.
  • It’s the best! and we are not the only ones to think that way. PowerBI ranked #1 BI tool in the famous Gartner Magic Quadrant in 2016 and 2017
  • It’s almost like Excel! Did I mention that PowerBI is the evolution of successive upgrades from ExcelExcel -> Excel + Pivot Tables -> Excel + PowerPivot -> PowerBITherefore Excel users will find it very natural and easy to transition from Excel to PowerBI. It will make it very easy to teach your coworkers about it.For example for 2 of the main functions you will be using:

Excel: VLOOKUP (Value, Table, ColumnNumber to look up)
PowerBI: LOOKUPVALUE (Column to look up, Table, Value)

or

Excel: SUMIF ( Column to sum, Filter)
PowerBI: CALCULATE ( SUM (Column to sum), Filter)

  • It’s getting better every month: The PowerBI teams releases monthly upgrades to their online service and to PowerBI Desktop. The pace of innovation is quite crazy, think about it: it’s as if you were getting a new version of Excel every month! you can discover more about the different updated on the PowerBI blog
    PowerBI is also a tool built based on the feedback of a community of thousands of users who are proposing ideas and voting for the best ones so that they can get picked by the PowerBI team to develop

So believe us, download PowerBI and start playing with it (we feel the word is not too strong). Surely you will soon be migrating all your Excel reports into it and next thing you know when asked for a quick ad-hoc analysis you will be using it as well and discover that Excel is really a tool from the past and that your life has changed…

We will cover more in detail PowerBI in next posts, stay tuned!

AnalystMaster