On a bar or line chart, show 2 hierarchies of the same dimension ?

[Groflo]

Hi,

I got a measure and 2 dimensions : time (year) and geography.

Geography is something like Region - Subregion - City - Block

What I'd like to have is a chat which, once filtered by geography at the City level, for example, could show the sum of my measure for that city AND the sum of my measure for the next level(s) : Subregion, and why not Region, to compare with each other. And if I can have that for my different years, it's even better.

But I don't know how or even if it's something one can do. If I add geography measure, it'll stick with the level filtered (Region if not filtered, other if filtered).

Someone can help ? Thanks a lot !

[kuba_michalik]

Assuming your measure is like Sum(value), perhaps you could show values for Region and Subregion by adding additional measures like this:
Sum({<City=>} total<Region> value)
Sum({<City=>} total<Region, Subregion> value)

I'm not 100% sure it would work, you'd have to test it.

However, the axis will still only show the level you are drilled down to (City)

[Groflo]

Hi,
Thanks for your answer.
If I copy your expressions and filter my app with a City, I do have the right datas for the city, but for the other levels, I only have the sum of my values unfiltered ! So the two high levels (Region, subregion) give the same result, that is to say the sum of my value for every region.
If you or anyone else does have an answer… thanks !

[kuba_michalik]

Yeah, that was a bit of a shot in the dark.

It's reasonably easy to do that (show region and subregion value) when you have only one city selected, or all cities are from the same subregion. Then my formulas need just a small modification:

For region:
Sum({<City=, Subregion=, Region=P(Region)>} total value)
For subregion:
Sum({<City=, Subregion=P(Subregion)>} total value)

However, if you are not filtered down to city level, or want to show cities across different regions/subregions, it won't work. You could maybe make it dynamic for showing additional bars for "sum of one level up", "sum of two levels up" and so on, but it would be a bit tedious. And of course the bars will be rather meaningless when you don't have any levels up 😉 (being on region level)

For the case of showing cities across multiple regions/subregions with extra bars for value on subregion and region level, I'm stumped.