Forum Discussion

karimm's avatar
karimm
Icon for Helper III rankHelper III
4 years ago

Presenting a hierarchy with pre-calculated values (without aggregation)

Hi All

Really appreciate your help on an issue I've been struggling with for many days...

Tried looking for an answer in other posts but reached a dead end.

 

I have data shown in the screenshot below that is small part of a child-parent hierarchy (actual one has more rows and levels)

 

 

As you can see, for each item, I have duration, planned progress % and actual progress %.

All these fields are pre-calculated in the data source.

I applied the parent-child pattern (https://www.daxpatterns.com/parent-child-hierarchies/) and got the following table:

 

The question is:

Is there a way to present this hierarchy in a visual without the visual aggregating on the 3 fields?

I need a visual that will allow drilling up/down in the hierarchy while displaying the pre-calculated values on each level.

 

I tried the matrix visual, and tried 2 different configurations:

Matrix-Configuration1

I apply the following configuration for the matrix (the values section is aggregating

 

Matrix-Configuration1-Result

When the hierarchy is fully expanded, we can see that Actual progress % of Parent1 is matching the original data (38%).

 

But when collapsing Parent1, the aggregation value (42%) is shown.

 

 

              Is there a way to request PowerBI matrix not to aggregate when row is collapsed? I’m ready to use any other visual if needed.

Matrix-Configuration2

Inspired by Excel’s Tabular Form layout in Pivot Tables, I tried putting all fields in rows bucket, and disabled stepped layout

 

Matrix-Configuration2-Result

When the hierarchy is fully expanded, we can see that fields are showing the actual numbers. Example: Actual Progress is 38%

 

But when collapsing Parent1, all values disappear.

 

Thank you very much in advance