Calculation at different level of Hierarchy

Position Against Target calculation differs at different hierarchy level
World Wide - Target is a rollup of all Territories


Drill Down to
Territory - Target is not a roll of all VP target for a given territory

Drill down to
VP - Target is not a roll up of Account Mangers for a given VP.

Drill Down to
Account Manager

I am looking for an elegant visual solution (dont give me multiple visual workaround) showing the correct calculation as user drills down the hierarchy

Many thanks

You can achieve this with drill downs.

You would add your fields to this x-axis in the order you want.

To drill down you would just click on the bar you want.

Hi Max, Drill down is not the issue.
Calculation for Position against target changes for every hierarchy.
Target is not a roll up like revenue.

Ahh I see.

I think I got it, but it’s not the prettiest with UI.

You need to make one parameter that will tell you the stage. And then separate parameters for each stage to filter for that stage. Finally, you’ll make two calculated fields that sets your target and your x-axis.

For my use case I have three stages: Client, Username, Device Model

First make a stage parameter: DrillDownStaging.

In my case my default is Client.

Next make filter parameters for each drill down stage. For me that would be Username and Device.

Next I am going to make an action for each possible drill down. This is where the UI won’t be perfect. Essentially you’ll be able to pick every single drill down option.

In each of those actions I am going to

  1. Change the stage name to the lower stage.
  2. Filter for the field value I selected.
  3. Update the x-axis for the stage.

(note we are going to change the DrillDownFilterUsername/Device Fields later)

image

image

Finally, I’ve made a target field that changes based on the stage and an x-axis field that changes based on the stage.

target = ifelse(${DrillDownStaging}=‘Client’,100,${DrillDownStaging}=‘Username’,80,50)

x-axis = ifelse(${DrillDownStaging}=‘Client’,{client_id[users]},${DrillDownStaging}=‘Username’,{username},{device_model})

After we have the x-axis let’s now change our graph to have the x-axis and our actions to change based on that x-axis.

image

Lastly, add filters to the upper stages after clicked on.

image

That should finally work.

Turns into this.

This

Turns into this

Again, not perfect but it will get the functionality of what you are looking for.

If you want to drill up you will need to essentially do the reverse.

Let me know if that helps

@Max Thats a brilliant illusion of Drill Down.
Just when i thought this is the fix , i realised about half a dozen menu actions after creating the reverse parameter.
I can see you have mastered the art of Parameters/Custom Action in Quicksight.
I will stick to my current view for now


But still keen know the complex calculation and simple visual solution like i did in Power BI.
Appreciate your help.
Are you on linkedin ?

@SrK1 thanks for the kinds words and no problem.

Yep I’m on LinkedIn. Here is my profile!

https://www.linkedin.com/in/maxvengelhard/

Hi @SrK1, following your reply to Max, I am marking his reply as, “Solution” . Thanks for posting your questions on the QuickSight Community Q&A Forum!