I came across DAX formulas around 6 months ago, while trying to create a client-friendly data table that was due to be delivered imminently. There were two key problems I was dealing with:
- The client-friendly data table was a separate table to the master sheet I’d been using – any changes made on one had to be made on another, meaning I would be doubling my work efforts.
- The data table still wouldn’t consolidate data in a way that was simple to read for the user, and still contained a huge number of columns, resulting in a fairly unfriendly amount of scrolling required.
Ultimately I was asking myself how do I make this information easily digestible for any stakeholder, technical or non-technical?
It was during this frustrating, bang-your-head-on-the-table-style internal discussion that I was introduced to DAX formulas. Since then, these formulas are always a consideration when finalising a document.
DAX stands for Data Analysis Expression. It’s a library of functions and operators that can be used to build formulas and expressions in Power BI, Analysis Services and Power Pivot in Excel data models.
For the purpose of this blog, we’ll be focusing on how you can use DAX formulas in your pivot tables to include text, combine multiple datapoints into one cell, and compare data for reporting purposes.
The three main project types we’ll be discussing are:
- Reporting
- Keyword Mapping
- On Page Optimisation
But first, lets discuss how we get set up.
Setting up your pivot tables for formulas
Before creating the formulas, we need to add the table into a data model. To do this, simply check the box for “Add to Data Model”. Be mindful that any major changes to the source table will break the data model, so once you create the data model, you can’t add new columns, delete columns, change column names etc.
How to add a DAX formula to your pivot tables
Once you’ve added your data to a Data Model, the next step is to create your formulas. To do this, right click on the table name in the PivotTable Fields and select “Add Measure”.
Once you’ve selected this, you’ll be presented with a window that you may put your formula into.
When including the formula, the two most important fields are “Measure Name” and, of course, “Formula”. Without using these two fields, the function won’t work:
One final call out before we get into the formulas themselves, is the “Check DAX Formula” button. This is a great addition that allows you to test any of your formulas before proceeding, and indicates where the error lies.
Once you’ve added your formula, and your happy with it, you can select OK. This measure will now appear in your PivotTable fields, and you’ll be able to drag them into your values box:
Now, lets look at some use cases for these formulas.
Using DAX to summarise your keyword mapping
Keyword mappings are a fantastic example of a project that can really benefit from the use of pivot table formulas. Often you have a huge amount of repeated data, that would be better suited in a pivot table, summarising pages and their target keywords
Summarising Keywords
For example, you likely have a multiple keywords being targeted by one target URL. This can be far better summarised if we can group the keywords by their target URL path.
[TABLE – tab “Summarise keywords & SV]
Grouping the data would allow a far more user-friendly format, allowing non-technical stakeholders to digest the information far easier.
Grouping the keywords can be done with the following formula:
=concatenatex(table2, Table2[Keyword], “
“, Table2[Search Volumes], desc, Table2[Keyword], asc)
This uses a concatenation formula which concatenates multiple datapoints in a single column (highlighted blue), requests a new line as the delimiter (in DAX formula, you simply just create a new line between the “”), and then selects multiple sort by values.
Once you’ve inputted the above formula, the output should look like the below (NOTE: you need to wrap text for the new line delimiter to work):
Summarise Search Volumes
To expand on this pivot table we can also include search volumes. If you’ve already created the keyword lists in your pivot table, you can simply copy and paste the formula, replacing “Keywords” with “Search Volumes”:
=concatenatex(table2, Table2[Search Volumes], “
“, Table2[Search Volumes], desc, Table2[Keyword], asc)
Remember to use the same sort-by values, otherwise the search volumes won’t correspond with the correct keyword.
Summarise Title/H1 Recommendation
It’s likely that once you’ve mapped the URLs to your target keywords, you’ll want to start thinking about title and H1 recommendations.
When providing this in a data table, it results in duplicate recommendations throughout, as you’ve targeted multiple keywords to 1 URL.
[TABLE – tab “summarise titles”]
To remedy this, we can use the summarize function in a pivot table, so the title’s only shown once:
=summarize(table2,Table2[Title Recommendation])
This essentially summarises the data table by only including unique values in the table.
As well as being a fantastic way to showcase your recommendations, it’s also a fantastic way to see any errors with your data. For example, if you’ve accidentally added two titles for one URL, this will show up in your pivot table.
Summarise Top 3 Keywords
If you’re working with a large keyword research, you will likely find that some URLs are mapped to a 10, 20 or up to 30 keywords. In this case, you don’t always need to see all the keywords, and would rather see only the top 3 keywords.
In this case, you can use the “topn” formula in your concatenation:
=concatenatex( topn(3, table2, Table2[Search Volumes],desc), Table2[Keyword],”
“, Table2[Search Volumes], desc, Table2[Keyword], asc)
The topn function replaces the table with a filtered table based on how many results you want to include (in this case “3”) and what column you want to filter on (in this case “Search Volumes”).
This can be replicated if you also want to include the search volumes for the top 3:
=concatenatex( topn(3, table2, Table2[Search Volumes],desc), Table2[Search Volumes],”
“, Table2[Search Volumes], desc, Table2[Keyword], asc)
Only keywords with 50 searches/month or more
Like above, you may want to only see keywords with 50 or more searches per month. To do this, rather than using a filtering to select the top 3 keywords like we did previously, we simply create a filtered table that only includes keywords with search volume of 50 or more:
=CONCATENATEX(filter(master,master[Search Vol]>50), master[Keyword],”
“, master[Search Vol],desc)
Sum of Top 3 Search Volume/Revenues
In some cases, rather than finding the sum of all mapped keywords, you’ll only want to find the sum of the top keywords in the data set, as these will likely be what you optimise for.
=calculate(sum(table3[Search Volume]),topn(3,table3 ,table3[Search Volume],desc,table3[Search Volume],desc,[Keyword],asc))
In the above formula, the calculate function is required when indicating you want to do a calculation that includes filters in the data.
So the first part is the calculation itself (in the above formula, we’re using the sum function to add up all the search volumes) and in the second half of the calculation we have to indicate what the filtering is – which in this case is a filter to the top 3 results by search volume.
On page Optimisation
Concatenate keywords in multiple columns
Rather than providing keywords in a list, you may have selected the 3 target keywords, to help in providing you with on page recommendations.
[TABLE – tab “On page optimisation”]
Rather than this being provided in a somewhat messy data table, the keywords can be summarised once again by concatenating multiple rows:
=concatenate(concatenatex(table3,Table3[Keyword 1]),concatenate(“
“&concatenatex(table3,Table3[Keyword 2]),”
“&concatenatex(table3,Table3[Keyword 3])))
Including title, H1 and other text in the pivot table
Very simply, sometimes it’s beneficial to include text in your pivot table. For example, when if you’ve completed an onpage optimisation task, and are looking for ways to clean up the data, you can simply use the concatenate tool so text can be included in the Pivot Table:
=concatenatex(table3, Table3[Title])
Reporting period on period changes
Showing MoM Changes
Often we’ll be extracting data from either an API or a Datastudio report. It can be frustrating if you need to format the data before being able to make comparisons with a previous set of data.
For example, each month we generate a Google Search Console report using their API. This gets added to an existing data table, meaning the two data points we want to compare are in the same column.
query | clicks | impressions | Month |
seo agency | 45 | 3451 | Mar |
seo agency | 32 | 3229 | Apr |
If we want to know the percentage change in impressions month-on-month, there’s no easy way to create a pivot table that would provide that data, unless we use DAX formulas to do so.
Using the following formulas, we can extract that data without requiring some sort of data manipulation to get there:
Percentage Change
=divide(calculate([Sum of impressions],Table1[Month] = “Apr”) – calculate([Sum of impressions],Table1[Month] = “Mar”),calculate([Sum of impressions],Table1[Month] = “Mar”))
Absolute Change
=calculate([Sum of impressions],Table1[month] = “Apr”) – calculate([Sum of impressions],Table1[month] = “Mar”)