Tag: Data Analysis

Categories
Microsoft Power BI

Power BI – What is DAX?

Welcome to the world of data visualisation and analysis with Power BI! In this blog, we’ll dive into the heart of Power BI’s analytical capabilities: DAX measures.

DAX, or Data Analysis eXpressions, is a powerful formula language that enables users to define custom calculations in Power BI reports. Whether you’re a seasoned data analyst or just starting out, understanding DAX measures is key to unlocking insightful business intelligence. Power BI – What is DAX?

So, let’s explore some of the most popular measures, array functions, and time-intelligence functions, and see how they can transform raw data into meaningful insights.

Power BI – What is DAX?
Calculate Measure

Some often-used Power BI DAX measures include:

  1. SUM: calculates the sum of a selected column of data
  2. AVERAGE: calculates the average of a selected column of data
  3. COUNT: counts the number of rows in a selected column of data
  4. MIN/MAX: calculates the minimum or maximum value in a selected column of data
  5. DISTINCTCOUNT: counts the number of unique values in a selected column of data
  6. CALCULATE: modifies the context within which a DAX formula is evaluated
  7. FILTER: filters a table based on specific criteria
  8. RANKX: ranks values in a selected column of data
  9. YEAR/QUARTER/MONTH: extracts various time periods from a date column in a table

DAX measures allow users to perform complex calculations and analysis on their data quickly and effectively, providing valuable insights into trends and patterns in their data.

Array functions

Many measures in Power BI desktop need to be array measure, because of the data model structure in a Power BI model.

One of the powerful features in DAX is this ability to create array measures, which are measures that return an array of values instead of a single value. This can also be complicated for users, who haven’t been working with data cubs.

Here are some examples of DAX array measures:

1. DISTINCTCOUNT function:

DISTINCTCOUNT returns the number of distinct values in a column or expression. When used in an array formula, it returns a list of distinct counts for each value in another column.

For example, the following DAX formula returns an array of distinct count values for the “Product” column:

= DISTINCTCOUNT(Products[Product])

2. FILTER function:

FILTER returns a table that meets certain criteria specified in the expression. When used in an array formula, it returns a list of filtered tables for each value in another column.

For example, the following DAX formula returns an array of filtered tables for each value in the “Category” column:

= FILTER(Products, Products[Category] = EARLIER(Products[Category]))

3. VALUES function:

VALUES returns a table of unique values in a column or expression. When used in an array formula, it returns a list of unique values for each value in another column.

For example, the following DAX formula returns an array of unique values for each value in the “Category” column:

= VALUES(Products[Category])

4. SUMX function:

SUMX returns the sum of an expression for each row of a table. When used in an array formula, it returns a list of sum values for each value in another column.

For example, the following DAX formula returns an array of sum values for each value in the “Product” column:

= SUMX(Products, Products[Price])

Array measures are a powerful way to analyse data and gain insights from multiple dimensions at once. With DAX, you can build complex array formulas that can handle large amounts of data and answer specific business questions.

1. Sum of Sales by Year:

SUMX(

GROUPBY(Sales, Sales[Year], “SalesByYear”, SUM(Sales[Amount])),

SalesByYear

)

This formula groups sales by year and calculates the sum of sales amount for each year.

2. Average Sales per Customer:

AVERAGEX(

GROUPBY(Sales, Sales[CustomerID], “SalesByCustomer”, SUM(Sales[Amount])),

SalesByCustomer

)

This formula groups sales by customer ID and calculates the sum of sales amount for each customer. Then, it takes the average of all the customer sales.

3. Last Sale Date for Each Customer:

MAXX(

GROUPBY(Sales, Sales[CustomerID], “LastSaleByCustomer”, MAX(Sales[Date])),

LastSaleByCustomer

)

This formula groups sales by customer ID and finds the maximum date of sales for each customer, which represents the last sale date for that customer.

4. Sales Growth Rate by Month:

DIVIDE(

SUM(Sales[Amount]),

CALCULATE(SUM(Sales[Amount]), DATEADD(Sales[Date], -1, MONTH)),

BLANK()

) – 1

This formula calculates the growth rate of sales from the previous month. It uses the DIVIDE function to divide the total sales amount by the total sales amount from the previous month. Then, it subtracts 1 to get the growth rate percentage.

5. Running Total of Sales by Month:

CALCULATE(

SUM(Sales[Amount]),

FILTER(

ALL(Sales),

Sales[Date] <= MAX(Sales[Date])

)

)

This formula calculates the running total of sales by month. It uses the CALCULATE function to add up the sales amount for all dates that are less than or equal to the maximum date in the current filter context. The FILTER function is used to remove any filters on the date column that might interfere with the running total calculation.

Time-intelligence functions

DAX (Data Analysis Expressions) Time-intelligence functions allow users to analyse data over time, providing insights into key trends and patterns.

Some common DAX Time-intelligence functions include:

  1. TOTALYTD: This function returns the total value of a given measure from the beginning of the year up to the selected date.
  2. TOTALQTD: This function returns the total value of a given measure from the beginning of the quarter up to the selected date.
  3. TOTALMTD: This function returns the total value of a given measure from the beginning of the month up to the selected date.
  4. SAMEPERIODLASTYEAR: This function returns the total value of a given measure for the same period last year.
  5. DATESYTD: This function generates a table with all the dates within the current year up to the selected date.
  6. DATEQTD: This function generates a table with all the dates within the current quarter up to the selected date.
  7. DATEADD: This function adds a specified number of units to a given date.

Example: DATEADD Returns a table that contains a column of dates, shifted either forward or backward in time by the specified number of intervals from the dates, in the current context.

Syntax: =DATEADD(<Dates>,<Number of intervals>,<Interval>)

where the <Interval> can either be Year, Month, Quarter or Day

Power BI – What is DAX?

These functions can be used in combination with other DAX functions to create more complex analyses and visualisations.

Conclusion

As we wrap up our exploration of DAX measures in Power BI, it’s clear that these tools are indispensable for any data professional. From calculating simple sums to performing complex time-based analyses, DAX measures empower us to make data-driven decisions with confidence.

We encourage you to experiment with the examples provided and discover the full potential of DAX in your own business scenarios. Remember, the power of data is at your fingertips, and with Power BI and DAX, there’s no limit to the insights you can uncover!

Further Reading

Power BI –Power Query M functions versus DAX

How Power BI Can Change Your Business

 

Categories
Excel Training Microsoft

What-if analysis in Excel

Excel has some powerful tools that you can use to carry out a What-if analysis.

We sat down with one of our Excel Experts, Jens, to ask him about three particular data analysis tools and to see whether he would demonstrate how to use them for us.

Good morning Jens, you talk a lot about “what-if analysis”. To the beginner like me, could you explain what that means?

Excel offers three What-If Analysis tools that allow users to discover different scenarios and evaluate the effect of changes on their data. These tools help in analysing the possible outcomes based on different input variables values.

What if

Thank you, that’s clear enough. Would you mind starting by telling us what those three tools are?

The Scenario Manager

The first one, the Scenario Manager, allows users to create and save different sets of input values that can be applied to a worksheet. By defining multiple scenarios, users can quickly switch between different sets of data and observe the resultant changes in calculations, formulas, and charts.

Great, and thank you for the detail

Goal Seek

Next, Goal Seek is a modest sensitivity analysis tool in Excel. It allows users to determine the input value needed to succeed a desired result. The Goal Seek tool can work with changes in one variable. By specifying a target value, Goal Seek will find the input value that will produce the desired outcome based on a formula or calculation.

I can certainly see the value in that too!

Data Tables

The final tool, the Data Table, is also a sensitivity analysis tool in Excel enable users to perform multiple calculations simultaneously by changing one or two input variables. By creating a data table, users can explore different combinations of input values and observe the resulting calculations. Data Tables are specifically useful for analysing the effect of changing variables on complex models or large data sets.

 

So we can start to see a difference between the three tools from your explanation there. Particularly around when each one would be the most useful. Could I ask you to run through the three tools in a bit more detail, perhaps you could demonstrate how to use them in an example context?

What if Example 1 – The Scenario Manager

Of course. For the Scenario Manager, imagine that you have projected how many projects our company is going to run next year. Also, you have estimated the profit. But there are many uncertainties you want to consider. Your sales department has provided you with both good news and bad news. One of your VIP clients may be going to one of your competitors, but good prospects of getting some huge new clients can also affect your projections.

A planned marketing campaign end of this year can also have an impact on your projections.

What if

In this example the projections are 100 projects, with estimated average revenue for each project of £25,000 which results in £2,500,000 estimated total revenue – if you look at row 4 here on my spreadsheet.

20 employees are needed for the 100 projects with an average salary of £50,000 a total of £1,000,000 – this data is in row 7.

There is a fixed cost of £500,000 in row 10.

All projects are overseas, and it is not decided yet if the employees are going first class or economy class so both options are in the model. The profit is calculated to £510,000 if the employees are going first class and £750,000 economy class, as you can see from the £2.5m total revenue – the salary cost – fixed project costs – travel/ accommodation costs.

To build What if scenarios based on this model, you need to open the Scenario Manager.

Click Add and name the scenario. You will then need to tell the Scenario Manager which cells you would like to be able to change.

In this example you would like to change number of projects, average revenue, number of employees, average salary, ticket cost, hotel cost, and food cost.

The numbers in the model are the best guess scenario and it is very important that you keep the numbers you have in the model unchanged in a scenario otherwise you cannot get back to the scenario again later. When you click OK, you get the Scenario Values dialog box in which you change the changeable cells in your model.

What if

To create a new scenario just click Add:

Once you’re here, name the scenario click OK and enter the new values.

You can create any number of scenarios. In this example a Best Guess, a Best Case, and a Worst Case scenario have been built. To show the different scenarios select the scenario and click Show, and the model will show the values entered in your model.

What if

That’s great! Thank you for breaking it down as you have for us. It makes perfect sense. And thank you for demonstrating it in a way which aligns with how  I would use this tool. Shall we move on to Goal Seek?

what if Example 2 – Goal Seek

Okay. So using the same scenario, you have decided to use the economy class ticket option because your target profit next year is £2m.

The Goal seek tool can help you to understand what you will require to reach this target.

First, how many projects are needed. If I open the Goal Seek tool. The Set cell is the cell reference of the cell with the formula calculation the profit. Here it is B20. Enter the target (£2,000,000) in the “To value box”. In the “By changing cell” box, you can enter the cell reference of the cell you want goal seek to change. Here it is A4 where the number of projected projects is entered.

Now I just click OK, and the model shows in this example, that 156 projects are needed to reach the target.

What if

Instead of looking at number of projects required, you could also find out how much the average revenue needs to increase to reach the target. You will just have to change the “By changing cell” reference.

And the model shows that an average revenue of £37,500 will get you to the target.

What if

Amazing how much you are able to affect the data with such small changes, I can really see the value of this one too! Can we look at the third and final tool?

What if Example 3 – Data Tables

That would be the Data Tables. These can work with either one or two variables. We will be using 2 variables in this example.

We want to see how changes in the number of projects and changes in the average revenue will affect the profit.

To create a Data Table, you will have to enter the values you want to investigate in the cell next to the formula and across (here it is number of projects), and in the cell just under the formula and down (here it is average revenue). Select the whole range and open the Data Tables tool.

The Row input cell here is A4 and Column input cell B4.

Click OK and the Data Table tool will now show the profit based on all the values entered.

What if

Conclusion

It sounds to me Jens, that these are tools which many people will use on a daily, weekly, monthly, quarterly and/ or annual basis depending on industry and scale. I can certainly see the benefit of them when we are looking at data and trying to think holistically about our impact on the business!

Yes, the What-if analysis tools are very useful. Especially in forecasting models. Every time you have a calculation or a model in Excel and you have thoughts or questions like:

What if we raise our prices by 5%?

And what if we were to employ more people?

What if the fuel prices increase?

Whatever question which should popup when you look at your Excel models, the What-if analysis tools can help you gaining clarity of how changes can affect your models.