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
One common situation most users of Excel face in making recurrent reports -- daily reports, weekly reports, monthly reports etc -- is that of updating the raw data part of the report with the new data for the period they are creating report for.

As simple as this looks, it is often a boring, repetitive and time consuming task. It could mean having to copy and paste data from the new source data files every day. For some people who get data from different sources (branches, sales people, departments etc), that coping and pasting can easily become a 20 mins task with potentials for human errors.

How do you set up your report to automatically pull in new data from source files? PowerQuery is the answer.

And if you are wondering if this will work for data that you get over emails daily/weekly/monthly. Yes, it works for them too.

The major step is to always save the source files in a dedicated folder. Have a folder on your computer than will hold only those source data files. The files can be Excel files or text files (CSV, TSV etc).


Also, ensure that the files have same table column structure -- same table headers arranged in a consistent manner.

With all these done, you are half way.

In demonstrating this, I have a folder where I put in sales data from different branches of a Pizza restaurant. Everyday, I am required to do sales reports, trend analysis and target/KPI dashboards from all these sales data for management. Before, I would copy paste all the source data in a particular sheet on my daily report template. This could take between 5 to 15 mins depending on how tired/refreshed I was and if any interruption that makes me forget where I stopped.




Now with PowerQuery, all those boring aspect of the reports are fully automated away.

Instead of copying and pasting the content of the different source files into Excel, I go to Data menu, Get & Transform, Get Data, From File and From Folder.


Browse to the folder destination and select it.


You'll see all the files in the folder. Select Combine and Load, in the Combine options.


Pick the sheet that's got the data. That sheet name must be consistent across all the source data files.


Wait a couple of seconds while PowerQuery does its magic.

Notice how all the branches data have been pulled in.


And that's not the best part. The whole essence of my proposing PowerQuery is to handle tomorrow's source data and the days after.

So how does it work?

Very easy.

Just replaced the data files in the folder with tomorrow's data files. Then right click anywhere in the combined table data and select Refresh.

What if there's a new branch that just sent me their data after I had done all of these, what should I do to bring in that new branch's data? 

Again, very easy.

Include/save the new branch's data file in the dedicated folder. And click on Refresh in the combined table.

See an example below. 



We launched a new branch in Lokoja and after creating my report for the day, I suddenly got an email of the sales data for that branch and asked to include it in the daily report.

I simply add the branch data file to the dedicated folder and click on Refresh in my already existing combined table.



And that's all!

No more zombie-like copying and pasting.

You can watch the short video demonstration: https://youtu.be/mKEHb0OWuP8



Enjoy!

For all your Excel training needs, you can check us out at https://www.urbizedge.com/Excel or call 01-6311885 or email team@urbizedge.com


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
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
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

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