Hi all,
I am working on a metric set which has 3 measures and 2 dimensions (or hierarchies). Out of the 3 measures one of the measures is hidden (called, "Value"). Measure "Reduction" is calculated in the metric set by multiplying "% Change" with "Value". "Hierarchy 2" is unique per row, which are mapped to "Hierarchy 1", on a many to one basis.
I have applied a custom aggregation formula to "Reduction", SUM($Value$ * $Change$). I want the total to be calculated by individually multiplying the "Value" with the "% Change" for each "Hierarchy 2" value. This seems to work well when both the hierarchies are included (as seen in the first figure). When "Heirarchy 2" is removed, the custom aggregation fails, and seems to calculate "Reduction" as SUM($Value$) * AVERAGE($Change$) (see second figure).
I would like to create a metric set that displays only "Hierarchy 1" but with the aggregation calculated by performing multiplication row by row.
I would appreciate any feedback that would help me in overcoming this issue.