Search code examples
powerbidaxhierarchycalculated-fielddrilldown

Every Hiearchy level measure calculation and aggregating the calculation to higher levels

We have got a Power BI dashboard, where we have four levels in the hierarchy for sprint metrics. I am putting this simply as given below:

  1. Unit - Level 1
  2. Workgroup - Level 2
  3. Project - Level 3
  4. Iteration - Level 4

We will be having weekly Iteration. So, level 4 is at week level. Our Power BI dashboard is at week level.

We are having a 100% stacked bar chart. For every week, we want to see Unit status(Red, Orange, Green) -> Workgroup status (Red, Orange, Green) -> Project Status (Red, Orange, Green)

The Problem we have is, we are having a calculation to apply for each level.

  • For Level 2: If one project is red, workgroup is red. If 30% to 40% of projects are Orange, then workgroup is red. If 20% to 30% projects are Orange, workgroup is Orange. Else Workgroup is Green
  • For Level 1: If one workgroup is red, Unit is red. If 30% to 40% of workgroups are Orange, then Unit is red. If 20% to 30% workgroups are Orange, Unit is Orange. Else Unit is Green

In drill down reports, we need to have same metric (Project Status) traversing across levels.

How can we achieve this kind of different metric calculation at each level(Workgroup status based on Project. Unit status based on Workgroup) in a drill down report ?

Solution

  • We solved this problem by going for two drill-down reports, instead of using single drill-down report.

    The dashboard is at WorkWeek level. So, Workweek is the granuality of the dashboard. For each report the lower level is having the status.

    • Report 1: Drill-down report (Level 2 & Level 3): Workgroup -> Project. Here, Calculated column Project Status is mapped to Iteration Status of the workweek ( as granularity of the report is at workweek level). So, Workgroup 100% stacked bar chart will reflect, how many projects are in Red, Orange, Green.

    • Report 2: Drill-down report (Level 1 & Level 2): Unit -> Workgroup. Here, Calculated column Workgroup Status is calculated from Project Status, as per calculation. So, Unit 100% stacked bar chart will reflect, how many workgroups are in Red, Orange, Green.

    Report 2 (Drill-down report) -> Drillthrough using Workgroup name -> Report 1(Drill-down report).

    This way, we are able to achieve top down flow from Level 1 to Level 3.