KB42444: How to make metric and subtotal values use two different formulas at different attribute element levels with consolidation as a workaround in a report?

In some business scenarios, it is required to make metric and subtotal values use two different formulas at different attribute element levels with consolidation as a workaround in a report.
 
The following steps demonstrate how to design this kind of report in Strategy Tutorial project as shown below:
 

1. Create a consolidation "Category_Con" based on attribute Category. "Value of Music" = "Value of Books" / "Value of Movies". Format the value of Music element as Percent with two decimals, as shown below:

• Create a report with Category_Con in rows and Year and Revenue in columns. Run the report and insert grand total in columns. The total values at Books, Electronics, Movies and Music are directly summed from values of each year as shown below:

• In Data>Report Data Options>Calculations>Evaluation Order, set Total=1 and Category_Con=2, as shown below:

• This will make values are summed and then divided. That is, "Total value at Music level" = "Total value at Books level" / "Total value at Movies level" as shown below:

 
 
 
 
 
KB42444