Category: Power Apps

Posted on

Power BI – Quick Measures

Microsoft has prepared Power BI to make it easier for users to perform DAX measures without a lot of DAX knowledge.

To access the Quick Measures, click QUICK MEASURES on the Home tab. You will see a list of options to choose from. On the right side of the Quick Measure dialog box, you will find all the tables from your data model.

DAX

Example 1 – Rolling Average

Many use rolling average to smoothing the data set. Unusual periods can be disrupting for understanding patterns and especially in projections, they can make forecasts unnecessarily inaccurate.

In the example below a line chart visualises sales numbers over several years, but a number of periods were unusual and you want the audience to understand how the quantity would look under normal conditions.

DAX

From the dialog box you can select the Quick Measure ‘Rolling Average’ from the Calculation list. You will now need to add the fields from your tables and set up the parameters.

In this example the quantity is the Base value, Dates are added to the Date field (Rolling Average is a Time Intelligent measure and the primary key from the timetable needs to be added), and Periods before and after are set to 3 months (the smoothing level).

Measures

When you click OK Power BI will the write the DAX.

Quantity rolling average  =

IF(

ISFILTERED(‘Dates'[Dates]),

ERROR(“Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column.”),

VAR __LAST_DATE = ENDOFMONTH(‘Dates'[Dates].[Date])

VAR __DATE_PERIOD =

DATESBETWEEN(

‘Dates'[Dates].[Date],

STARTOFMONTH(DATEADD(__LAST_DATE, -3, MONTH)),

ENDOFMONTH(DATEADD(__LAST_DATE, 3, MONTH))

)

RETURN

AVERAGEX(

CALCULATETABLE(

SUMMARIZE(

VALUES(‘Dates’),

‘Dates'[Dates].[Year],

‘Dates'[Dates].[QuarterNo],

‘Dates'[Dates].[Quarter],

‘Dates'[Dates].[MonthNo],

‘Dates'[Dates].[Month]

),

__DATE_PERIOD

),

CALCULATE(SUM(‘Line_Items'[Quantity]), ALL(‘Dates'[Dates].[Day]))

)

)

 

By adding the Rolling Average to the line chart, you can see the result below.

Measures

Example 2 – Percentage difference from filtered value.

The line chart below (the columns) shows sales in 3 different countries Canada, Mexico, and United States. It shows how much is generated in sales by Canada and Mexico by percentage.

The line is calculated by another Quick Measure – Percentage difference from filtered value.

Measures

In this example the Base Value is a sales measure. You can define how you want this quick measure the handle blanks. You can display them as blanks or you can tell the measure to treat blanks as zero. In this example the measure is filtered by country, and we have selected United States.

Measures

And as in the first example Power BI will write the DAX.

sales % difference from U.S.A. =

VAR __BASELINE_VALUE = CALCULATE([sales], ‘Customers'[Country] IN { “U.S.A.” })

VAR __MEASURE_VALUE = [sales]

RETURN

IF(

NOT ISBLANK(__MEASURE_VALUE),

DIVIDE(__MEASURE_VALUE – __BASELINE_VALUE, __BASELINE_VALUE)

)

Example 3 – Correlation Coefficient

In this example correlation between product unit prices and sales quantity needs to be investigated. Does the price affect the quantity sold?

The quantity and unit price have been added to a scatter chard below. The trend line will indicate to us the relationship between the two. The dots are spread out. This indicates that there isn’t a close relationship between the two variables, but what is the correlation coefficient?

A Quick Measure can very simply find the result.

Category here is the products identified by the field Product ID. Measure X and Measure Y are the here the sum of quantity and sum of unit price.

Again, Power BI will write the DAX.

Sum of Unit Price and Quantity correlation for Product ID =

VAR __CORRELATION_TABLE = VALUES(‘Items'[Product ID])

VAR __COUNT =

COUNTX(

KEEPFILTERS(__CORRELATION_TABLE),

CALCULATE(SUM(‘Items'[Unit Price]) * SUM(‘Line_Items'[Quantity]))

)

VAR __SUM_X =

SUMX(

KEEPFILTERS(__CORRELATION_TABLE),

CALCULATE(SUM(‘Items'[Unit Price]))

)

VAR __SUM_Y =

SUMX(

KEEPFILTERS(__CORRELATION_TABLE),

CALCULATE(SUM(‘Line_Items'[Quantity]))

)

VAR __SUM_XY =

SUMX(

KEEPFILTERS(__CORRELATION_TABLE),

CALCULATE(SUM(‘Items'[Unit Price]) * SUM(‘Line_Items'[Quantity]) * 1.)

)

VAR __SUM_X2 =

SUMX(

KEEPFILTERS(__CORRELATION_TABLE),

CALCULATE(SUM(‘Items'[Unit Price]) ^ 2)

)

VAR __SUM_Y2 =

SUMX(

KEEPFILTERS(__CORRELATION_TABLE),

CALCULATE(SUM(‘Line_Items'[Quantity]) ^ 2)

)

RETURN

DIVIDE(

__COUNT * __SUM_XY – __SUM_X * __SUM_Y * 1.,

SQRT(

(__COUNT * __SUM_X2 – __SUM_X ^ 2)

* (__COUNT * __SUM_Y2 – __SUM_Y ^ 2)

)

)

 

The correlation between unit price and quantity here is -0.15. In other words when a product gets more expensive the sold quantity decreases. But a negative correlation of -0.15 isn’t much. Nevertheless it could be a clever idea to keep an eye on this number over time to understand the sales pattern.

Conclusion

Microsoft has given their clients a shortcut to create DAX measures by offer the quick measure tool. Quite complicated measures can be achieved without prior DAX knowledge.

Posted on

Clean data in Power Query to improve efficiency (part 1)

Have you ever pulled in data from various places to restructure it in Excel? Have you found yourself repeating this long, manual process every month when new data is added? The solution is to use Power Query. This blog is the first in a series of four. We will show you what Power Query can do to save you lots of time and help you become more productive. Let’s see how we can clean data in Power Query to improve efficiency (part 1).

What is Power Query and why is it useful?

Power Query is an app which transforms data sourced from many different locations before it is loaded into Excel. It works by making a connection back to the original data source. This means that whenever changes are made, they are saved as ‘Applied Steps’. These are stored in memory. The benefit is that a simple refresh in Excel is all that is needed to update any changes to the original data.

How to use Power Query
  1. In Excel, open a dataset with a column structure that needs some cleaning. In the example below, the ‘Full Name’ field has text entries where unwanted spaces need to be removed e.g. ‘ Anna Brown’

  1. Ideally the data needs to be a table before launching Power Query. To create a table, select a cell within the dataset and go to HOME > FORMAT AS TABLE. Select a coloured thumbnail and click OK in the ‘Create Table’ box
  2. Connect to the source data: DATA > FROM TABLE/RANGE. (NB. For any data that is external to Excel, go to DATA > GET DATA and browse to the specific data source)
  3. The following window appears:

  1. Note the ‘Applied Steps’ section on the right that will expand to include further steps
  2. Now select the ‘Full Name’ field, right click, and go to TRANSFORM > TRIM.

Right click on the ‘Extract Numbers’ field and go to SPLIT COLUMN > BY NUMBER OF CHARACTERS:

  1. Enter ‘2’ for ‘Number of Characters’ and select the radio button to split: ‘Once, as far right as possible’. Click OK
  2. Double click on each of the split columns and enter ‘Region’ and ID_no.

Now apply the changes back to Excel:

  1. In the Home tab, go to CLOSE & LOAD > CLOSE & LOAD TO > TABLE. Click OK to load the table to a new sheet
  2. Now in a new row under ‘Full Name’ add ‘ Mike Orange’ – with 2 leading spaces and under ‘Extract Numbers add ‘North19’
  3. Finally in the new sheet, go to QUERY > REFRESH and check the new data has changed

Power Query in Excel can connect to a vast range of external sources as well as within Excel itself. These include other Excel workbooks, financial systems, databases, websites, SharePoint, and many others.

Conclusion

Power Query is a powerful and extremely useful tool. It allows you to repeat routine cleaning tasks on your datasets without having to do it all manually. This tool will therefore help you become more efficient in the way you manage data which ultimately increases profitability. This concludes ‘Clean data in Power Query to improve efficiency (part 1)’.

For course details on our Power Query course at STL, please click on the link below:

https://www.stl-training.co.uk/syl/300/excel-power-query.html

Check out this useful article on data trends:

https://www.inc.com/john-hall/5-trends-in-data-emerging-in-2020.html