Different measure for each drill down level in VA

[rufmau68]

i have a simple hierarchy (continent, state...) and 2 measures (revenues, items number)

i needs to see (in a cross table, or other objects) just the items number if the user drill to continent, but i need to see the revenue measure if the user drill down to state category

[FredrikE]

Hi!

It's not working out of the box!

You can somehow determine which level you are at by counting distinct level members, but it failes if the sublevel only contains 1 item, i.e 1-1. And from that create a calculated aggregated meassure that uses either item or revenue depending on the flag. 

Why not use two list tables one for items number and one for revenue and link them together?

//Fredrik

[rufmau68]

thanks FredriK,

the workaround with 2 table list object , i'm sure that it work, with interation between the 2 object

but i'm interested to understand if there is a way to know the drill level where the user lands....

your first suggestions its interesting but i'm not sure that i understand it, what U compare ?

eg.: hierarchy categories ---> lev1, lev2 and lev3

distinct(lev1) vs. .... ?

[FredrikE · Accepted solution]

I tried to do "Count distinct" on level2.

When on level 1 I got a value greater than 1 (Level 2 need to have more than one member thou').

*When on level2 I got the value 1 for each row.

I then created a calculated aggregated meassure that did an "If-then-else" and picked measure 1 or 2 depending on the "count distinct" variable.

Maybe you can do some more calculations to be sure that you are on the correct level?

Why I suggested the two list tables is that I don't really like showing different measures in the same column/table, I think it will confuse the users. I have done a lot of this using OLAP cubes and MDX, but it always got complicated and hard for my (especially new) users to understand.

I would try to separate it and create filters/interactions to get the same outcome 🙂

//Fredrik

[rufmau68]

I tried,

great! it works

in that way U change column contents, but not column header... so U need to insert any dynamic disclaimer to suggest the user about the really column content (drill by drill...), but its a good way!!!!

now in the week end i try to have a dynamic column number in the drill...

and in this case i try using measure not stored in column of a source table , but stored in rows of a sourece table

thanks a lot

bye

Mauro