CODEWITHSUNDEEP
powerbidaxpowerquery
Suetam016
Suetam016

Reputation: 101

PowerBI (DAX) : Dealing with Company Hierarchy and Values

I have 3 tables: Agents, Leaders and Super-Leaders. These tables have NAME, ID and RESERVE_VALUE columns, as well as the ID of the superior hierarchy.

I am trying to simply make a Matrix visual showing the total RESERVE_VALUE under each context of hierarchy and I'd love to able to drill down this table to show the Agents' RESERVE_VALUE bellow the Leader's and Super-Leader's value on the same column if possible.

I linked the tables and simply put the 3 reserve columns side by side on the matrix, but that is kind of an ugly solution.

I am currently making a flat table using the Leaders as categories, and the hierarchy works fine, but I can't figure out how to deal with the values.
I researched a lot, watched some videos, tried using ISFILTERED, HASONEVALUE, HASONFILTER, etc.. but the problem is that even when I can get the lower level right, the upper levels' values go blank.

The similar problems I've seen online are normaly related to products and categories, but the main difference is that categories simple agregate the products' values. In my case the Leaders do agregate the values, but they also have theyr own values that I want to display when drilling down.

Has anyone been through something similar in PowerBI? Any suggestions on how to solve it or how to research this problem better?

Here are the sample data:

FLAT TABLE:

Agent   LEADER  SP_LEADER   AGENT_VALUE LEADER_VALUE    SP_VALUE
A   L1  X   100 300 0
B   L1  X   150 300 0
C   L2  Z   200 0   700
D   L2  Z   370 0   700
E   L3  Z   0   340 700

DESIRED RESULT:

Desired Result for each level, if that is even possible

The original files are like these:

NAME_AGENT  LEADER  AGENT_VALUE
A   L1  100
B   L1  150
C   L2  200
D   L2  370
E   L3  0

LEADER Table:

NAME_LEADER SUPER_LEADER    LEADER_VALUE
L1  X   300
L2  Z   0
L3  Z   340

SUPER LEADER Table:

NAME_SP_LEADER  VALUE
X   0
Z   700

Upvotes: 1

Views: 368

Answers (2)

Suetam016
Suetam016

Reputation: 101

So I just wanted to do a follow-up in case someone has the same problem. I solved it using a decomposition tree visual that is native to PowerBI (I had to update it). It is an amazing tool to use, people loved it.

The only thing is: I had to create a flat table with the structure and in the cases that the values were related to the superiors (Leaders / SP Leaders) I just filled the lower hierarchy columns with strings like "Value of the superior". With this method, when you go to the lowest level of the tree you can see the values from the subordinates and also the value that is related to the superior.

Upvotes: 0

davidebacci
davidebacci

Reputation: 30304

In PBI, you can control what the total says at each level using ISINSCOPE() but what you cannot do is change what the total says depending on whether the node is expanded or contracted.

What you want to do isn't possible nor is it a good user experience for the value of a row to change depending on whether the level is expanded or collapsed. For example, you are trying to treat L1 as a leaf node with an individual value when expanded but as a parent node when collapsed.

If you turn off stepped layout, you will see why this makes no sense as L1 is only ever a parent.

My suggestion is you rework your data. If L1 is the team name and L1 is also an individual contributor, you should have L1 form part of the hierarchy. e.g. You have A=100, B=150 and L1(individual member)= 300 and then your L1(team) will always show 550 at the total level.

Upvotes: 1

Related Questions