Summarise dimension hierarchy without nest mode

Hi All,

UPDATE: Found the solution, hopefully this may help someone else in the future. Please scan to bottom for the solution.
Issue
I was just wondering is there a way to group the text values of a table and aggregate the sales values? For example we have a GL division codes like this:

What I want to do is exclude the dimension key (the numbers ) and have a summary of the other properties grouped, when I change the properties to only show the text, it is not grouping together and keeping them separate (for example below you can see “France” is there twice rather than once). I understand this is due to different dimension keys, but I ideally only want to show the row once and sum the totals:

I know I can change the DB design to accommodate this, but would be nice to know if there is a way using the existing hierarchy. It needs to stay as a table, so nesting is not an option.

Solution
The reason this failed to work was because in the database design I did “Hide Code” on the dimension design of “Division”. Once I enabled this and rebuilt the solution, the grouping now works correctly. as below:

Everyday is a school day :slight_smile:

Thanks in advance

1 Like