Show different data on different matrix layers

[Anonymous]

Hi guys, I am trying to build a report where my co-workers can see how much effort was initially estimated for a work item and eventually the amount of hours the spend in total. Moreover it needs to show the different levels of these work items (epics, features, PBI and tasks). Each individual work item knows if they have a parent and which one that is, so I separated each level into a new column. But now I can't show the different efforts on each different layer in a matrix.

Can someone help me with this issue?

[jdbuchanan71 · Accepted solution]

@Anonymous 

You can use a SWITCH with ISINSCOPE to detect the active "layer" and sum from the correct table.

Measure = 
SWITCH(
    TRUE(),
    ISINSCOPE(PBI_Workitems[ID]),SUM(PBI_Workitems[Work Effort]),
    ISINSCOPE(Feature_Workitems[ID]),SUM(Feature_Workitems[Work Effort]),
    ISINSCOPE(Epic_Workitems[ID]),SUM(Epic_Workitems[Work Effort])
)

You want to work from the bottom up so start with checking if the most granular level table first.

I have attached my sample file for you to take a look at.