Hello! I have a grouped table. I will have 2 metrics. For each metric I want to display the result as a percentage. I can do this with 1 metric, but I would like to have two columns in the table (account and benchmark). So I am planning to create a custom metric which would be SUM of the GROUP / SUM of the TOTAL. I am struggling to make this work. As a first step I am building a metric which gives the sum of all groups on each row. Similar to the way I could for an SQL window function. In my example I expect that column CW Sum Over Test should have the value 1133.58 for all rows, which is the total of the individual groups. However as you can see in the screen print, it is showing a completely different sum. Also attached is a screen print of the custom metric. Any advice would be gratefully received.

Hi Rob, do you need the "by all in all other dimensions" here? This clause overrides all attributes - keeping the metric from being broken down at all. As a result, the associated metric will remain aggregated at the highest possible level of aggregation across all dimensions.

Thank you @Joseph Heun - I do, yes. So that's what is surprising to me, I had expected adding that clause would have expected that by adding that clause I would see the totals 😕

You may need to add the attribute you are slicing by in the metric to the report itself in this case.

Thank you @Joseph Heun. I'm not sure that I understand what you mean? I am slicing by the RTNG Level 2, which is in the report?

It's a bit difficult to see what is going on without the LDM and the full metric definitions. Could you also provide how account contribution is defined?

@Joseph Heun - there is no calculation behind Account Contribution. IRL it is the percentage of the account's market value which a given position (ie row in our raw data) represents. There are some cases where not all positions will have a category, so rather than using the raw percentage, which may add to like 50% categorised and 50% uncategorised, we want to re-weight as through we had 100% coverage. Or to put this another way we might have a portfolio which is 30% fixed income, we want to get to what proportion of that 30% each instrument makes up. I checked the "show missing values" checkbox for my attribute, so I now understand where the 1200.62 is coming from (how the first row is 2401.24 is a new mystery - it's clearly doing the sum in the metric and then summing 2 rows of the metric together which is surprising to me, although I suppose that 2 empty values might not be considered as equal) So I guess the next question is how do I make the metric respect the same implicit "nullness" filter that the report is using.

You can try with the IFNULL clause, and filtering out based on what you are showing in the screenshot, but adding those missing values directly in the metric may not be possible at this time, and we can submit this for product feedback for you on your behalf.

Thank you @Joseph Heun, please do submit the product feedback. In the meantime, I am going to solve this by providing the percentages in the underlying view.