Customizing values using calculations
It is not possible to edit values that are overwritten by a calculation.
In some scenarios, using a single DAX measure will not suffice to display a value in the report. In those scenarios, it is useful to be able to override a value using a calculation.
An example of when this is required is when you are working with percentages. If the visual was provided with a percentage, then it might display values correctly on a detail level but will struggle to display a correct total since the data the visual receives is always a flat table.
Let's consider an example where you show percentage margin for products.
Product | Production Cost | Selling Price | Margin | % Margin |
|---|---|---|---|---|
A | $10 | $20 | $10 | 50% |
B | $15 | $25 | $10 | 40% |
Total | $25 | $45 | $20 | ? |
Using the percentage margin directly would result in the visual showing the sum of margins: 90%. This is clearly not accurate. One might consider averaging them, but this approach will also yield incorrect results: (50% + 40%) / 2 = 45% whereas the true total margin is $20 / $45 = 44.4%. (See Simpson's Paradox)
The calculation will be correct only if the visual can evaluate the result for each hierarchy level. To achieve this, we must configure a "calculation" for the measure.
To add a calculation for a column, follow these steps:
- Enter Edit-mode
- Click on the column section that contains the percentage value
- Click on the value itself
- In the value-properties panel, click on "Edit Expression"
You should now see the calculation editor. From here, we can create the expression that will replace the default calculation. The expression we're looking for is "the sum of margin divided by the sum of the selling price". To create this, we'll take the following steps:
- Click on "Add Expression"
- The default aggregation for expressions is "sum of" which suits our needs, so we'll leave it at that. You can read more about available aggregators further down.
- Click on the field which it should sum up and select the margin field.
- We'll then need to add the division. By hovering your cursor over the row, you will see a small + sign appear. Once clicked this will give us the option to add more expressions.
- Select "divided by" > "expression"
- Then, update the field of the divisor to be the selling price.
Once you're done, preview the report and note that the value is now being displayed correctly!
In the example above, we illustrate how you can apply an expression to an entire column. It is also possible to configure calculations for specific rows, and even specific columns in rows.
To add a calculation for a row, simply click on the field/section which you'd like to override values in, and then click on Add Calculation. You will be presented with a dropdown containing the different values that your column sections contain, as well as the option to override all.
Calculations applied to a section will be applied to all the hierarchies within that row-section. Calculations applied to specific fields will only apply to that specific hierarchy level.
The calculation builder comes with the following aggregators:
Aggregator | Short name (Used in advanced editor) | Remarks |
|---|---|---|
sum of | sum | Only works on numeric values. Aggregation on non-numeric values will yield "NaN" - Not a Number |
average of | average | Only works on numeric values. |
count of | count |  |
first value of | first | Picks the first value. Does not need to be numeric. |
last value of | last | Picks the last value. Does not need to be numeric. |
distinct count of | distinctCount | Fetches the number of unique values. |
maximum of | max | Returns the largest numerical value. |
minimum of | min | Returns the smallest numerical value. |
Operation | Short name (Used in advanced editor) | Remarks |
|---|---|---|
plus | + |  |
minus | - |  |
multiplied by | * |  |
divided by | / | Returns blank if division by zero occurs |
strictly divided by | s/ | Will return "Infinity" if division by zero occurs |
concatenated with | concat | Will concatenate the provided values. |
Operation | Short name | Remarks |
|---|---|---|
absolute value of | abs |  |
negation of | negate |  |
natural logarithm of | ln |  |
exponential of | exp |  |
reciprocal of | reciprocal | Will return Infinity if the input is 0. |
square root of | sqrt |  |
rounded value of | round | Uses JavaScript Math.round() which uses round half up (toward infinity). |
Every aggregate expression can be filtered to only include a subset of the data. To add a filter, simply click on the small filter icon to the right of the expression.
The editor that appears is the same as for row-/column-filters. See Filtering values in sections for more details.
