Hello! We have our reporting per city & city dimension has region as a property. When I am trying to create a report to show margins for example for our group I want to start with the group margin, then the region and then the city. However, the formula which is on a city level doesn’t seem to work for the margin percentages for the region & group. It works only if I create different metrics with different formula on the different level dimension but then my report can’t have the sequence I want. Is there any solution here? Is there a way to fix this with ADD?
Page 1 / 1
Hi Marina,
If I understand correctly you’re trying to display a percentage (margin, so revenue / costs) on several levels of hierarchies. Pigment doesn’t currently support ratio-type aggregators in views, so what you’re seeing is the absolute sum of percentages over your hierarchy (e.g. France has Paris - 12% Margin and Toulouse - 20% Margin, you get France at 32%, which is just 12+20).
What I suggest is that you rather use a Show Value As in your table, where you add both Revenue and Costs as independent metrics. You can then right click on the Revenue and select Show Value As → % of → Metric (choose Costs)
The end result: the third column is a Show Value As
If you do not wish to display the absolute information (Revenue, Costs) you can right click on them and hide them. This will only hide them but not remove them from the view, which is useful because then Pigment still can do a Show Value As.
For context, here I’ve displayed in rows City, City > Country and have activated all aggregators (sum). I have activated the tree view and displayed k€ as a suffix of the first two metrics.
I hope this helps! Please tell me if you need anymore details.
Hi Francois thanks for the help! To be honest I have tried that and is indeed convenient to show margins for current year. But then I have another issue. When I want to estimate the YoY margin delta eg margin current year % - margin prior year % because both margins are calculated items, I cannot make the difference because they are not available as metrics to deduct one from the other. Unless there is also a workaround for this?!
Hi Marina,
Unfortunately, the calculated items currently calculate Offset, Cumulate and ratios, but not much else - and you can’t do a Show Value As of a Show Value As. I agree it would be nice to have some finer displayed calculations too!
In the meantime, you can either try and fit with the current Show Value As or calculate your values in a metric, with the relevant granularity. Be aware that it won’t work on aggregated data, e.g. showing the sum on all months of the year.
Just like you suggested in your first message, the solution for complex formulas is to do different metrics at different levels at the moment.
Thanks Francois. I guess I will wait for the product team to add this calculation too :)
