Microsoft Office Tutorials and References
In Depth Information
Because you are defining a calculated column, you do not want any filter other than the Model
column. For this reason, the ALLEXCEPT function has to be used as the filter parameter of the
CALCULATE call so that only the rows that have the same Model column of the current row
are considered in that calculation. So the ProductOnModel calculated column just requires
a simple ratio:
ProductOnModel = Sales[SalesAmount] / Sales[ModelSalesAmount]
In Figure 9-3, you can see the results of the calculated columns defined in the Sales table.
FIguRE 9-3 Calculated columns for product percentages of total and model.
The formulas you have seen in this section can be easily adapted to any scenario where data
is stored in a single denormalized table. Having normalized data in more tables requires some
changes in the formula, which you are going to learn in the next section.
Calculating Ratio on Multiple Normalized Tables
Let us consider a different scenario, in which data is normalized in multiple tables. Most of the
time, you have a table containing numeric measures that you want to aggregate, and you have
other tables describing attributes of these measures in a more verbose way. For example, in
Figure 9-4, you can see the SalesAmount measure in the Sales table. For each row, there is a
ProductID column that refers to the corresponding row in the Products table, which is shown
in Figure 9-5. You can find the following example in the CH09-02-RatioMultipleTables.xlsx
workbook on the companion DVD.
FIguRE 9-4 Sample data in a Sales Table.