We want to analyze how invoicing works in our company, and we would like to find out which service category is the one we’re selling the most and which we could improve. So, we pull data related to all invoices we’ve sent to clients, and we find the following table:
As you notice, invoice line description is not consistent or standard across invoice details so if we want to use the column as is, it will be difficult to analyze data properly.
We need to categorize each row into the 4 following groups:
- Adjustments
- After Hours Labor
- Projects
- Ongoing support
Create a Category – Keyword table
First, let’s think about the rules we want to apply. For example, if line description contains “Project – “ then we want to categorize this as “Projects”, or if it has the keyword “Adjustment”, then this line should go to “Adjustment”, and so on. For this scenario we will focus on the following four groups and corresponding keywords
Add a calculated column in the invoice lines table
We have two tables:
- Invoice Details
- Keywords
We want to check the “invoice_description” column in each of the rows and see if any of the keywords listed in the Keywords table are contained in this description.
If there is a keyword that matches, we want to get which category the match is for.
Billing Category =
//current line text, to lowercase
VAR currentrowtext =
LOWER ( 'Invoice Details'[invoice_description] )
//search all keywords and find if any match
VAR searchrow =
MINX (
FILTER (
Keywords,
SEARCH (Keywords[Keyword], currentrowtext, 1, 0 )
//search current row in the keywords table and filter if there is any match
),
Keywords[Category]
//return the first found category
)
//check if there is any match, if not, return "Other"
RETURN
COALESCE ( searchrow, "Other" )
Use the new column in your visualizations
Now we’re more than ready to start analyzing our data with the new category table we added!
