Call 0700ANALYTICS, +234-808-938-2423, +1-941-312-2149 or Mail: info@UrBizEdge.com or Visit: www.urbizedge.com | Privacy Policy | Terms and Conditions

# Register for the next special Business Data Analysis and In-depth Excel Training

## Friday, April 26, 2019

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

## Sunday, April 21, 2019

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

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!