Weighted Average with hierarchy at different levels

[trevrobwhite]

I'm trying to create a clustered column chart (and rador) with drills but I want the weighting to be different at each level, let me explain

ID	Area	Weighting	Q1 2023	Q2 2023
ID	Identify	70%	60%	65%
ID.AM	Asset Management	60%	70%	80%
ID.AM-1	Device Should be secure	90%	80%	85%
ID.AM-2	Software inventory	10%	30%	40%
PR	Protect	30%	40%	50%

Some people may recongonise this as the NIST Framework (which is what it is), the data is just an example.

You can see at the top level, ID (Identify) makes up 70% of the weighting with PR (Protect) making the remaining 30%, but then within these categories there is further weighting to each areas, then there is a final third layer doing the same.

I've structured the data showing the actual % value and the adjusted value for each weighing.

What I'm struggling with is telling PowerBI to use the weighted values in the dataset and don't try and calculate itself by drilling into the children, I almost need it to filter at each level.

The result should be something like this comparing each quarter which can be drilled into each section.

Any hints/tips on how to do this would be much appreciated

[MFelix]

Hi @trevrobwhite ,

How do you know in wich level you are? Do you have any way to identify the different levels?

If you have you must use the ISINSCOPE to pick the correct level and then get the result you need to be on the visualization.