Power BI DAX #5: SUMMARIZE

I once facilitated a Power BI training where the participants needed to generate a new table from the sales transaction data (that comes from the ERP) in such a way that they would see all the sales regions, products, Year-Month and total sales quantity.

They already have a table of targets by products for each region. 

Ordinarily, we could have just created a relationship between the transactions data and the target table and leveraged that with measures to create whatever type of report needed. But as it was a training and they needed to experience all the different ways of doing a particular task, we decided to create a table that will extract all the regions, products by regions and total sales volume present in the sales transaction data.

To achieve that, we used the DAX formula: SUMMARIZE

So here's what the sample data I am going to use look like:

And the new table Formula I'll enter is:

Branch_Product_Sales = SUMMARIZE(

'Pizza Sales Data',

'Pizza Sales Data'[Branch],

'Pizza Sales Data'[Pizza Sold],

"Total Sales Quantity",sum('Pizza Sales Data'[Quantity])

)

You can read up on the official documentation of SUMMARIZE: https://docs.microsoft.com/en-us/dax/summarize-function-dax 

In essence, how the syntax works is you put in the table name, the columns you want to pick unique items from (combines across the columns, though, which makes it show all the different products for the different branches repeating branches as many times as necessary to show products in that branch) and you add the aggregation formula to run on a column with the values to do a computation on (indicating the name to give the generated column).

There's a more complex use of SUMMARIZE that involves using ROLLUP and/or ROLLUPGROUP. You can get a sense of what they do by going through the already given documentation link.

Here's a short video to demonstrate the formula: https://youtu.be/X8mqjSQ9gOo

Enjoy!

And to join our Power BI training class, visit https://www.urbizedge.com/powerbi or call 01-6311885 or email team@urbizedge.com

Power BI DAX #4: CALENDAR, every sales/operations analyst friend

If there exists a book of ten commandments for Power BI, one of the commandments will be -- always have a date table.

A date table is a table that has just dates, and maybe any derivative of a date (month, quarter, year etc.)

Why do you need a date table?

Two easy/quick reasons:

1. You have many tables with date columns in each. It is better to have one table to include all the date periods you need and be related to all these other tables.
2. Even if you have just one table, as long as that table can have date gaps or 1900 for some records or 2090 for some records; you'll want to have your own better controlled date table rather than battle endlessly with wrong date entries in source data table. This is common when old data are migrated from a legacy tool.

So how do you create a DATE table easily in Power BI?

The answer is CALENDAR.

If I know how far back I want to go, then I would just specify that date as the start date and today as the end date.

Some other times, I would let CALENDAR use FIRSTDATE to get start date and LASTDATE to get end date.

Other times, I would specify a specific date and specify a specific end date (not TODAY, could be a future date).

For this illustration, I would use a specific start date and TODAY as the end date. Power BI will always increment the dates everyday -- that is the magic of putting TODAY as end date.

Calendar = CALENDAR(DATE(2016,01,01),TODAY())

Notice I used DATE to put in the specific date.

You can watch the YouTube tutorial: https://youtu.be/oodi9Vz0eeQ

Enjoy!

And to join our training class, visit https://www.urbizedge.com/powerbi

Power BI DAX #3: VALUES, another must know DAX formula

One common situation sales analysts face is capturing newly introduced products or SKUs or Region or Sales Rep in their reports. In Excel, you'll have to do something extra -- and if you are not an Excel ninja, that something extra can be very significant and time costly.

With Power BI, you might not need to do anything extra if you are good with using VALUES.

CASE STUDY
You are the sales analyst for Mega Pizza. Everyday you report sales by branch. But every couple of months, a new branch is opened. On the ERP the sales for each new branch is automatically captured, but on your report you'll have to include this new branch and assign it the general target given to all branches.

How will you achieve this automatically in Power BI without having to do any extra/manual intervention when a new branch is live.

ANSWER: VALUES

First, a screenshot of what the transaction data from the ERP looks like.

We are going to create a new table from this table extracting the unique list of branches from the ERP live data table. And that's exactly what VALUES does. 

VALUES is the DAX formula that extracts a unique list of records from a table or column. You can read more about it from Microsoft Documentation: https://docs.microsoft.com/en-us/dax/values-function-dax

There is also DISTINCT but VALUES allows for referential integrity check which makes it a better choice for most tasks. If you read through the documentation link I gave you'll get a more thorough explanation of what I mean by referential integrity check and practical difference between VALUES and DISTINCT.

And here's how to use VALUES for the scenario I just described.

Branch Targets = VALUES('Pizza Sales Data'[Branch])

And to finally make it meaningful. I add a New Column that assigns the target all branches are given (assuming Mega Pizza doesn't discriminate in its target allocation). :)

You can watch the YouTube tutorial: https://youtu.be/pLZhm0R2KrU

Enjoy!

And to join our training class, visit https://www.urbizedge.com/powerbi

Power BI DAX #2: Meet the most popular DAX formula -- CALCULATE

Anyone wanting to do any serious data analysis with Power BI must be very good friends with CALCULATE.  Most books and online resources will tell you that CALCULATE gives you power over the filter context, enabling you to modify it as you wish. Example of one such online resource is Microsoft Documentation: https://docs.microsoft.com/en-us/dax/calculate-function-dax 

I, however, have an easier to digest way of explaining it to people entirely new to Power BI and don't know what context means.

I assume you are already very familiar with Microsoft Excel. 

In Microsoft Excel, when you need to do a formula, you simply go into one cell and type out formulas that typically give you back one result which will show in that cell.

That is the usual usage pattern. However, there is another way of inputting formulas in Excel. If you convert the Excel data into table (Format as Table), any formula you type in the table works across the table entire rows. You don't have to drag down the formula.

How does all these relate to Power BI?

Well, the more intuitive way of entering formulas in Power BI is the second way I mentioned. Any formula you enter gets computed for the entire rows in the table. It's called New Column.

But what if you want to calculate the total sales for Enugu branch, like I did in the first Excel screenshot? Well, one has to do a New Measure, as that is what gives you one value rather than values as many as the number of rows in our table. In Excel, this would be easily solved with a SUMIF.

In Power BI, there is no SUMIF nor COUNTIF nor AVERAGEIF nor SUBTOTAL. Rather what you have is this amazing formula called CALCULATE. It makes it possible to extract out the rows/records that meet conditions you specify and then you run any normal aggregation/expression on the extract (like SUM, AVERAGE etc). So it is your SUMIF, COUNTIF, AVERAGEIF and many more common formulas you use in Excel. In fact, it is more than all of them. It does near magical things.

So how does CALCULATE work?

You specify the aggregation/expression you want and then put in filter conditions to narrow down to just the very records you want to run that expression on. 

In this very example I have shown via screenshots, here is the CALCULATE formula I entered:

Enugu Sales = CALCULATE(SUM('Pizza Sales Data'[Amount Sold]),'Pizza Sales Data'[Branch]="Enugu")

And that's how CALCULATE works.

However, don't be deceived by the simple straightforward example I have used. As you progress in your Power BI journey you will definitely have to get comfortable mixing CALCULATE with FILTER, ALL, ALLEXCEPT and many other DAX formulas. That's where the true mastery and magic lie. Also, you should read up on row context and filter context in Power BI.


You can watch the YouTube tutorial: https://youtu.be/Vzro-tgDr_8

Enjoy!

And to join our training class, visit https://www.urbizedge.com/powerbi

Power BI DAX #1: The Magic of CROSSJOIN

Last week I facilitated a training where the participants were staff of Nigeria Bottling Company (makers of Coca-Cola in Nigeria). After my usual prepared practice, we delved into making the types of reports they need. 

One of the reports had a very interesting twist that CROSSJOIN was perfect for. For confidentiality, I won't tell you the exact situation we had regarding the said report but I have created a simpler and easier to relate with example that I will use to show you the magic of CROSSJOIN.

CROSSJOIN is a DAX formula that generates the cartesian product of all the rows in tables inputted into the formula argument. You can go through Microsoft's official documentation on it at https://docs.microsoft.com/en-us/dax/crossjoin-function-dax 

CASE STUDY
ABC Limited has five branches across Nigeria and sells four products. Each product has same target across all the branches.

Branch	Manager
Lagos	Jide Aje
Abuja	Ahmed Kazeem
Kaduna	Sanni Eze
Port Harcourt	Joe Opoi
Enugu	Chika Nwabueze

Product	Target
Product A	100
Product B	120
Product C	90
Product D	130

You are to generate a new table that will combine these two tables into one -- having all four products with assigned targets showing for all the five branches.

Branch	Manager	Product	Target
Lagos	Jide Aje	Product C	90
Abuja	Ahmed Kazeem	Product C	90
Kaduna	Sanni Eze	Product C	90
Port Harcourt	Joe Opoi	Product C	90
Enugu	Chika Nwabueze	Product C	90
Lagos	Jide Aje	Product A	100
Abuja	Ahmed Kazeem	Product A	100
Kaduna	Sanni Eze	Product A	100
Port Harcourt	Joe Opoi	Product A	100
Enugu	Chika Nwabueze	Product A	100
Lagos	Jide Aje	Product B	120
Abuja	Ahmed Kazeem	Product B	120
Kaduna	Sanni Eze	Product B	120
Port Harcourt	Joe Opoi	Product B	120
Enugu	Chika Nwabueze	Product B	120
Lagos	Jide Aje	Product D	130
Abuja	Ahmed Kazeem	Product D	130
Kaduna	Sanni Eze	Product D	130
Port Harcourt	Joe Opoi	Product D	130
Enugu	Chika Nwabueze	Product D	130

So how do we achieve this?

ANSWER: CROSSJOIN

If you want to follow along and recreate this tutorial, just launch Power BI. And under Home menu, click on Enter Data. Copy paste or type in the Branch table data.

Repeat same steps for Product table.

And to the magic step: under Modeling menu, click on New Table and type CrossJoin Table = CROSSJOIN('Branch Table','Product Table')

And voila! You are done.

BONUS: What if you needed to do this in Excel?

You will have to use PowerQuery's Unpivot, and maybe more than once if both tables have more than 2 columns of relevant data.

You can watch the YouTube tutorial: https://youtu.be/A_2GM4Ig24k 

Enjoy!

And to join our training class, visit https://www.urbizedge.com/powerbi

How To Scrape Data From A Website Into Power BI (AbokiFX as an example)

Power BI is an amazing tool. I have used it to create amazing auto-updating dashboards for different reporting needs. You can view a couple that I made publicly available: https://www.nigerianelite.com/Shares/Analysis, https://community.powerbi.com/t5/Data-Stories-Gallery/Sales-Dashboard-for-a-Pizza-Restaurant/m-p/509025#M2208 and https://community.powerbi.com/t5/Data-Stories-Gallery/Nigerian-Stocks-Market-Analysis-Live-Report/m-p/175769#M850

Today, however, I will be showing you how to use Power BI to scrape data from a website and create a report/dashboard from the scrapped data. I will illustrate with AbokiFX.com data on parallel market FX rates for Nigeria. Effectively, you will be creating a report like the one below:

You can view the interactive version at https://community.powerbi.com/t5/Data-Stories-Gallery/Nigeria-Parallel-black-Market-FX-Rates-from-Abokifx-com/m-p/568355#M2372 and you can download the editable source file at https://drive.google.com/file/d/1emrcWqpSnBaiYntrvV9ecnUYEcVJ9I3i/view?usp=sharing 

STEP 1

From an empty Power BI desktop file, do Get Data and pick from Web (under Other).

Follow through with providing the URL of the website to pick data from and selecting the table of data to pull into Power BI.

STEP 2

Do a couple of transformation on the data (except you are very lucky that the pulled data is already in a form that suits your report).

STEP 3

Create the report you want and publish.

You may also want to configure a scheduled refresh to make Power BI keep pulling new data from the website everyday.

Business Intelligence using Power BI and Business Data Analysis Using MS Excel Training in Lagos, Port Harcourt and Abuja

(You can help forward to anyone you feel would benefit from this. Thanks.) 

The business world is now data driven and every business professional must now be fluent in the language of data. William E. Deming had the most accurate way of portraying this new age: “In God we trust; all others must bring data.” Without data skills you will have a tough time influencing in the business world. You must learn to manipulate data, make compelling data stories, leverage data for insights and drive management decisions with rich actionable dashboards.

UrBizEdge Limited, Nigeria’s leading business data analysis company is putting together these special training for proactive business professionals. It's intended for Sales Managers, Financial Analysts, Project Managers, Business Analysts, Data Analysts, NGO program managers, MIS Analysts, Strategy Analysts, Business Intelligence Analysts, HR Executives and power Excel users.

The training will be facilitated by a Microsoft recognized Excel and Power BI Expert who is also a Microsoft Certified Trainer and the only Microsoft Excel Most Valuable Professional (MVP) in Africa (there are just about 125 in the whole world and it is the highest level of recognition from Microsoft to an industry expert). He has authored bestselling books on Data Analysis using Power BI and MS Excel. We have had participants of our training from Citi Bank, Standard Chartered Bank, Dalberg, SaveTheChildren, Mobil, TCN, Chevron, Vodacom, Nestle, Christian Aid UK, Guinness Nigeria, Nigerian Breweries, Delta Afrik, LATC Marine, Broll, Habanera (JTI), Custodian and Allied Insurance, SABMiller, IBM, Airtel, Diamond Bank, Promasidor, eHealth Africa, ECOWAS, Biofem Pharmaceuticals, Ministry of Finance, FMDQ, Schlumberger, Palladium Group, Nokia Siemens Networks and DDB.

To register reach Michael on 08089382423 and mike@urbizedge.com or Hannah on 08021180874 and hannah@urbizedge.com or Emmanuel on 09084825064 and emmanuel@urbizedge.com to register. There is a class size limit.

Power BI Training Date: Friday 15th June 2018 to Saturday 16th June 2018

Venue: Kristina Jade Learning Center, 70b Olorunlogbon street, after Banex Hotel, Anthony Village, Lagos. 

Abuja Date (MS Excel): Friday 8th June 2018 to Saturday 9th June 2018

Abuja Venue: Hotel Rosebud, 33 Port Harcourt crescent, off Gimbiya street, Garki 11, Abuja.

Port Harcourt Date (MS Excel): Friday 22nd June 2018 to Saturday 23rd June 2018

Port Harcourt Venue: Aldgate Hotel, 20B King Perekule street, GRA Phase 2, Port Harcourt, Rivers state. 

Lagos Date (MS Excel): Friday 29th June 2018 to Saturday 30th June 2018

Lagos Venue: Kristina Jade Learning Center, 70b Olorunlogbon street, after Banex Hotel, Anthony Village, Lagos. 

The Business Intelligence with Power BI training outline is:

1.     Power BI’s strength and weakness compared to the other popular BI tools
2.     Important concepts of Power BI: real-time insights, drilldown, drill through, management dashboards, live connection, data governance, real-time collaboration, going from data to insights and from insights to action, trigger alerts, controlled access etc.
3.     Connecting to any type of data source (from structured to unstructured which will require some transforming)
4.     Getting data from existing services, organizational content pack, flat files and live databases
5.     Data Transformation (very broad and requires some knowledge of data analysis) and we will use DAX and M formulas too.
6.     Creating relationships between the datasets and leveraging hierarchy (a.k.a. data modelling)
7.     Creating Reports
8.     Visualizations and the science behind choosing the right visuals
9.     Importing custom visuals (especially word cloud for sentiment analysis and other very useful non-native visuals)
10.   Creating dashboards
11.   Publishing Reports from the Power BI Desktop and pinning to dashboards
12.   Scheduling refresh and configuring data gateways
13.   Q & A Natural Language query
14.   Integrating with Cortana
15.   Live Dashboards
16.   Collaboration and sharing
17.   Printing and Export to PowerPoint
18.   Analyzing the dashboard data (report) from Power BI service in Excel (new feature)
19.   Value Proposition to corporate customers
20.   Use case scenarios for entire company or business unit or departments
21.   Access from mobile app and setting data alerts (automated notifications when something of note happens)
22.   Lots of interaction and practice

The Excel based Data Analysis training outline is:

1) Data Manipulation in Excel 

We’ll show you, from a consultant expertise level, how to manipulate data in Excel. From data preparation/cleaning to data formatting the professional way. We’ll cover both the science and the art of data manipulation in Excel, and share very useful keyboard shortcuts and expert tricks that will speed up your productivity in Excel.

2) Data Visualization and Presentation in Excel

A picture is worth a thousand words. And in the business world, it is often the only way to not bore your audience and pass the valuable message you’ve uncovered in your data analysis. We will teach you the foundations of data visualization – from the different types of charts to when to use each of them. Then we will work through business samples to learn the art part of doing data visualization right. You will learn the rules of business data reporting via charts and gain from our industry wealth of consulting for businesses in this vital area.

3) Large Data Analysis: Pivot Table, Pivot Chart and PowerPivot
You should never say you know Excel if you don’t know how to use Pivot Table. It is that important. It is Excel’s premium tool for analysing large data – sales data, inventory data, HR data, transaction data and most business operations data. We are going to cover from the basics to the very advanced use of Pivot Tables. We will show you how to create dynamic reports with Pivot Table; how to overcome some of its layout issues; how to turn off the distracting controls in your final report; how to create calculated fields; how to create Pivot Charts; and the special tricks only a full-time Excel consultant can show you that will turbo-charge you Pivot Table skills. Then we’ll show you how we analyse data of up to (and even above) 30 million rows in Excel. Yes, in Excel. Heard of PowerPivot?

4) Business Data Analysis 

In this section, we teach you the secrets that separate the analysis experts from the people with head/academic knowledge Excel. It is one thing to know the different tools in Excel and it is another completely different thing to know how to expertly mix them together to creatively deliver value at high speed. As full-time Excel chef, we will show you the secret ingredients that make companies consult us even when they have Excel super users in their organization.

5) Executive Dashboards and Reporting
This is the level self-knowledge will not get you to. How do you create an uncrowded insightful visualization for a report with 1000s of rows? Then how about for your sales analysis report of many products and regions? How do you show the different interactions in your data? In short, how do you bring your data to life and take it from a boring confusing mass of text to an interactive exciting visualization? You have to come to get the answers.

6) Excel to PowerPoint

Management level reports are best presented in PowerPoints. When you’ve got a delicate story to tell, you have to guide your audience through a one idea/insight a slide PowerPoint. We won’t teach you how to design slides but we will teach you how to make your chart slides speak very loud. You will also learn the tricks of linking your PowerPoint charts to Excel. It will help cut down the hours you spend on weekly/monthly PowerPoint reports. We will also show you how to embed Excel files in your PowerPoint slide. No more sending separate Excel files when you can embed them right on the very slide you reference their data.

7) Excel VBA
Forget about all you’ve heard about Macros or VBA. Let’s show you how easy and exciting it is to break into the Excel VBA world.

To register reach Michael on 08089382423 and mike@urbizedge.com or Hannah on 08021180874 and hannah@urbizedge.com or Emmanuel on 09084825064 and emmanuel@urbizedge.com to register. There is a class size limit.

For a taste of our high quality content, view our free tutorial videos at www.urbizedge.com/tutorials