How often when using Excel have you had to scroll down to find what you’re looking for in a huge spreadsheet only to find your top headings disappear off the screen? Well worry no more. The solution lies in using an incredibly handy tool called Freeze Panes. This tool ‘freezes’ the top row and left column headings making it easier to check data against the appropriate headings when scrolling. This can increase your productivity and save you so much time
What are Freeze Panes
The images below show 2 separate ways to ‘freeze’ panes:
Freezing top rows, scroll down
Freezing left columns, scroll right
When freezing panes there are 3 options:
Freeze top row (i.e. row 1)
Freeze first column (i.e. column A)
Freeze both rows and columns
The first 2 options can only be applied separately whilst the 3rd option applies the ‘freezing’ in both directions.
How to apply Freeze Panes
To apply Freeze Panes to the top row (row 1) go to VIEW > FREEZE PANES > FREEZE TOP ROW
For the first column, go to VIEW > FREEZE PANES > FREEZE FIRST COLUMN
To ‘freeze’ both rows and columns select the cell B6 (in this example). This means when you go to VIEW > FREEZE PANES > FREEZE PANES, any rows above and to the left of cell B6 will be ‘frozen’ when scrolling in either direction
After ‘Freeze Panes’ has been applied, you may wish to turn off this feature. This may be because you might want to reset the areas to be frozen or you just want to take it back to normal. To do this, go to VIEW > FREEZE PANES > UNFREEZE PANES
If you add more columns and/or rows to your spreadsheet, you will need to ‘refreeze’ the panes. For example, adding 1 column to the left would mean selecting cell C6 and then going to VIEW > FREEZE PANES > FREEZE PANES
Conclusion
‘Freeze Panes’ is a great time saving tool that will help you organise data in large worksheets and will therefore make you more efficient in viewing your data.
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.
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.
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).
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])
By adding the Rolling Average to the line chart, you can see the result below.
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.
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.
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.” })
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])
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.
