Apply a formula to metric sets with compatible time hierarchies
If you have two metric sets, each based on its own data cube, and each data cube utilizes the same or compatible time hierarchies, then you can apply a formula to the two metric sets without having to further join your data. For example, one data cube has a ModifiedDate hierarchy while the other data cube has an OrderDate hierarchy. In this case, the unique names of the hierarchies are different but if both are based on the same underlying time hierarchy (e.g. they were both replaced with the default time dimension), then formulas can be applied to this data without further joins.
This article describes features available in Dundas BI 2.6 or later.
2.1. Create two data cubes
Create a new data cube with 1 measure (LineTotal) and 1 hierarchy (ModifiedDate).
Replace the ModifiedDate hierarchy with the Year > Month > Day default time dimension hierarchy.
Create a second data cube with 1 measure (OrderQty) and 1 hierarchy (OrderDate).
Replace the OrderDate hierarchy with the Year > Month > Day default time dimension hierarchy.
2.2. Create two metric sets
Create a new metric set to display the first data cube as a line chart.
Create a second metric set to display the second data cube as a line chart.
2.3. Create a dashboard and add a formula visualization
Create a new dashboard using the Blank template. Drag both metric sets to the dashboard canvas.
Go to the toolbar, click Data Visualization, and then click Formula Visualization. A formula visualization applies a formula to existing data but generates the output result in the form of a new visualization.
In the formula bar, enter a formula for dividing the two chart series. Instead of typing the unique name of each measure, simply click the line series on a chart. The fully-qualified name of the corresponding measure will be entered in the formula bar.
Click apply. The output result will appear as a table visualization. It will have the two input measures as well but you can remove these from the table (metric set).
Re-visualize the table as a line chart.
This will also work with formula functions.