Process phases grouping and visualization

[ddorhout]

Dear Forum,

I am running into an issue. I would like to have process phases visualized for each product that is going through those phases.

I have the status of the lowel level phases, but I want to roll it up to a higher level (so 1.1, 1.2, to 1). 

Also I want to visualize it, and that the user of the visualization can click through when phase 1 has the status of "in progress" to see further details. 

How do I do that? I have a summary of the data below.

Another part of the question is maintenance of this table. the names of the process phases may change, is there a solution that I do not need to update my queries repeatedly when the name of a phase changes? The numbers stay equal, I was thinking to normalize the data but that only works for rows.

Your help is well appreciated 🙂 

Product	1.1. Kick-off	1.2. Assign Stakeholders	1.3. Fill out form	1.4. ...	1.5. ...	2.1. Review form	2.2. ...	2.3. ...	(To be calculated) - roll-up phase 1	(To be calculated) - roll-up phase 2
A	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
B	No	No	No	No	No	No	No	No	No	No
C	Yes	Yes	Yes	No	No	No	No	No	In Progress	No
D	Yes	Yes	Yes	In Progress	No	No	No	No	In Progress	No
E	Yes	Yes	Yes	Yes	Yes	Yes	No	No	Yes	In Progress
F	Yes	Yes	Yes	Yes	Yes	Yes	In Progress	No	Yes	In Progress

[ddorhout]

The solution is working as a charm. However, now I am facing the next issue: how do I add another state such as "Not Needed"? Now, when not everything is on "Yes", it gives "In progress". However, how do I make sure the "Not needed"s are excluded from this "In progress" calculation. I am struggling with PowerQuery.

[parry2k · Accepted solution]

@ddorhout I have attached the solution, please tweak it as you see fit. There are two parts to this solution: 1) preparing the data in PQ to optimize the model and 2) measure to visualize the data at different granularities.

Cheers!!

[ddorhout2]

This looks really good! How did you do that?

Perhaps I would prefer to transpose the rows to the columns, would that be possible?

[parry2k]

@ddorhout I guess you are looking something on these lines:

Viewing using the product on rows, roll up, and sub-level on columns with the ability to slice

Using rollup level = in Progress and seeing at rollup level:

Roll up level = in Progress and looking at sub-level