Effect of hierarchies included on custom aggregation

#1

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.


Image title
bentk6PPQIG8NF8MQkzZ_Hierarchy%20and%20Custom%20Formula.png896×374


#2

To achieve the intended result, you can set the width of “Hierarchy 2” to “0” so that it is not displayed in your table visualization but it is still used to aggregate the values in your table.

For example, I have similar table with two Hierarchy “ModifiedDate” and “ProductID” along with formula measure as follows:

<!--[if !vml]-->

Image title


To hide the “ProductID” from the table visualization, I will set it’s width by navigating to Properties -> Row Header column - > ProductID -> Width (set to “0”) as follows:

<!--[if !vml]-->

Image title


The final result will look similar to one shown below:

<!--[if !vml]-->

Image title
kuWANneOTAq5YKBz4eil_p3.png586×579


I hope this helps.

#3

Thank you, Ajitkaur.

This is very helpful.


As an end result, I would like to have one row per value of ModifiedDate. I tried to collapse all the rows, which seems to aggregate fine. But when I check-in that metric set, or pull in that metric set in another dashboard, it expands all the rows. Would you have any tips on how to achieve that?


#4

I could not find an example or how to on the support site, I hope they take note and fix that.

You have to open the (use the pencil icon) date on your metric set and go to the bottom and set the level and top level. Setting this both to day should be what you want (just show day level with out and expanders).


Image title