Referencing Field Names

When you reference a field name inside a Charticulator expression, you use the field name with no qualifying table name. However, if the field name has spaces in it, you must surround the field name with the grave character ( ` ) which is situated top left of your keyboard, to the left of “1” and under “Esc.” You can concatenate field names in a text mark by using the plus (+) symbol and insert a space by using double quotes between the space. In Figure 8-1, you can see how field names are referenced when bound to the Text attribute of the text mark.

You may remember that in the previous chapter, we looked at using “Tick Data” on a numerical axis where you reference a field name that will label the axis. This is one occasion where there is no field bound to the attribute, and therefore you can just type the field names into the attribute. For instance, in Figure 8-2 we’ve concatenated the “Wine” field and the “PricePerCase” field, separated by a space. Notice that you don’t need an aggregating function (e.g., the function “first”) because the aggregation is already being expressed in the “Data” attribute of the plot segment.

It’s interesting to note that although the space character requires double quotes surrounding it, the dash character does not.

Using Charticulator’s Aggregate Functions

Just like DAX measures, Charticulator supports functions that return scalar values such as “sum,” “avg,” “max,” “min,” “count,” “stdev,” “variance,” “median,” “first,” and “last.”

Expressions Using Categorical Data

Let’s start with the text expression that is used when you bind a categorical field to an attribute. For instance, in Figure 8-3, we’ve added the “Salespeople” field to the Fill attribute of a rectangle mark and the Text attribute of a text mark. When using categorical data, Charticulator uses the “first” function.

Charticulator, as all Power BI visuals, must use a function to retrieve a single value, but because you can’t aggregate text values, it uses the “first” function by default.

Expressions Using Numerical Data

When you first bind a numerical field to an attribute, Charticulator will use the “avg” function by default. For instance, in Figure 8-4 we’ve bound the “Sales” field to the Height attribute of a rectangle shape.

The reason the “avg” function is used is because, unless you use the “Group by…” attribute, each shape represents a single row from the underlying data. Therefore, expressing the average of one value is that value (as indeed would using “sum,” “max,” “min,” “first,” or “last”). In this case, there is no advantage to changing the function. The exception to this is when you group your data using “Group by….”

What you can also do in these numerical expressions is to use simple arithmetic within the braces. This can be useful to express numerical values in units such a “K” or “M” in the Text attribute of text marks. For example, in Figure 8-5, you can see two versions of a text mark showing the sales value, one showing the full value and the other expressing the same value in the “M” unit. You can see how the version expressed in “M” saves valuable space on the canvas.

In Figure 8-5, you can see that the calculation divides the “Sales” value by 1,000,000, and this calculation sits inside the braces.

Expressions Using Grouped Data

If you are using the “Group by…” attribute of the plot segment, then you can edit the function according to the calculation you want to plot. For instance, in Figure 8-6, we’ve grouped by the “Year” field and bound the “Sales” field to the Height attribute of a rectangle. We have also added a text mark to show the value of the “Sales” field. We have then changed the function used by the Height attribute from “avg” to “min.” To do this, we clicked into the Height attribute and selected “Min” from the function dropdown. Notice that the text mark has also been edited to reflect the function being used by the Height attribute; see Figure 8-6.

It’s important to note here that these are aggregations of the data behind the visual that you see when you view the data in the Fields pane, as shown in Figure 8-7, not aggregations of the source data. In other words, our expression is calculating the minimum of the aggregated sales for each year for each salesperson (e.g. 278,519 for “Abel” in 2017).

In Chapter 11, I’ll show you how you can use the “Group by…” attribute to great effect in your chart designs.

Formatting Numerical Expressions

When you bind numerical data to a text mark, Charticulator will apply a default numeric format using one fixed decimal place, indicated by the format string inside the curly braces; see Figure 8-8.

Charticulator uses a numeric formatting syntax based on the “d3-format” specification used by JavaScript. You may never have heard of “d3-format,” but have you ever created custom Excel formats? If so, you’ll be familiar with the idea of placing format specifiers in specific positions to determine the format of a number (e.g., Excel’s custom format uses “positive ; negative ; zero ; text”). Well, d3-format is a bit like that, only a little more complicated. Here are the d3-format specifiers:

​[[fill]align][sign][symbol][0][width][,][.precision][~][type]

You can find more information regarding this syntax here:

https://d3-wiki.readthedocs.io/zh_CN/master/Formatting/

For a currency format, just type the currency symbol at the start of the expression. If you want to use a dollar sign, precede it with a forward slash (  ); see Figure 8-9.

There is an alternative way to generate currency formats that we explore in the last section (see the section on “Using the DAX FORMAT Function” below).

Formatting Tick Labels

You can use any of the preceding format specifiers to format a numerical axis, a legend, or a data axis (see Chapter 14 for details of the data axis). Use the “Tick Format” attribute and type the required format into the attribute, remembering to enclose the format in curly braces. An example of formatting the y-axis using the dollar currency format is shown in Figure 8-10.

Note the use of the currency symbol typed outside the braces.

Using the DAX FORMAT Function

You might be relieved to know that there is an alternative to using d3-format. Using a DAX measure, you can create an alternative version of a numerical field that converts the field into a text string that includes the numerical format you require. To do this, you create a new measure and use the FORMAT function. You can see an example of creating a currency format using DAX in Figure 8-11.

The measure that defines the format is added to the data that comprises the chart and so it then shows in the Fields pane. It can then be used in the Text attribute of a text mark.

For more information on the DAX FORMAT function and format strings that you can use, follow this link:

https://docs.microsoft.com/en-us/dax/format-function-dax

I appreciate that all of this has been a bit dry. Nevertheless, it’s very frustrating if you don’t know how to format your numerical data, and Charticulator does not make it very user-friendly! However, in this chapter, you have learned how to reference field names and how to work with expressions involving both categorical and numerical data. You also now understand a little more about d3-format and can use it to format numerical expressions for the Text and the Tick Format attributes.

I know that you’re itching to get back to building visuals, but before we can do so, there’s a mandatory topic that we have yet to address. In order to move forward with more challenging charts, we need to get to grips with the rather odd world of Charticulator’s scales and legends, which are the subject of the next chapter. I know we’ve already been using legends, and we’ve touched on the workings of the Scales pane, but like always with Charticulator, there remains a lot more to say. I don’t think you’ll find that very surprising.