Hierarchical Asynchronous Relationships Question

[SteveO1967]

Hey all, mandatory "new to PowerBI." I have a good background on traditional data analysis, but looking to build a demo of a management system.

So let's say we have a VP who measures certain business performance critieria annually. We know that her outputs are a result of all the people working under her. Her Directors measure things that relate to these outputs, say quarterly. In turn, these Directors have Senior Managers that measure linked metrics, say monthly. And all the way down.

How would you recommend visualizing and connecting these metrics? I can see how to do such things if they are synchronous, but what if Arnold measures 10 things daily that affect Betty's 10 weekly metrics, that affect Chuck's 10 monthly metrics that affect Doreen's 10 quarterly metrics that affect Edna's 10 yearly metrics?

I know how I would analyze these relationships, but not how to set it up in PowerBI to respect the heirarchy and "rolled up asynchronous" nature I have described.

Thanks in advance for any guidance you can give me!

[v-juanli-msft]

Hi @SteveO1967 

I'm not clear about your requirement.

It is better to post sample data and expected result as this forum guide suggested.

There is a sample of Customer Profitability where a CFO can see key metrics about their five business unit managers (executives), products, customers, and gross margins (GM). 

Best Regards

Maggie Community Support Team _ Maggie Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SteveO1967]

OK, here is a demo I built.

https://drive.google.com/open?id=1Hq_FsgkKTfExq5FcCJ7H6NxElc0wfWBs

The tabs "Level 1" through "Level 4" indicate the metrics for different levels of management. The idea of course is that if we are good at selecting our metrics at each level, they should be somehow connected to the next level of management. So Level 4 is the process, Level 3 front-line managers, etc. Each level manages in a different time scale, so our Level 4s are tracking data hourly, Level 3s daily, Level 2s weekly, and the Level 1 monthly. Each level is interested in some metrics. The naming convention is Level.Metric. So Management Metric 1.2 is Level 1's second metric of interest.

In order to model this, I created a pivot table between each level that averages by the time horizon for the next level up, then made up mathematical relationships using those averages for creating metrics for the next level up, plus a little noise. Obviously this is WAY more deterministic than reality, but this would be the ideal state as a demo.

Now in reality, I don't know anything other than the numbers I see on each of the Level tabs, but I do hypothesize that each level is controlled by the next level down, and so on. So the data are related, but gathered at different time scales. I think I understand how to roll up and summarize data by different time scales, but this is something different. This is looking for relationships across asynchronously gathered data.

In the past, I have collected the data at each level, displayed them on a variety of charts for each level so they can manage, and run maybe a CHAID, general linear model and/or multiple regression analysis to see if, say, Level 4's data relates to Level 3's data. We end up finding some things which validates or disproves their inirial hypotheses about how much their metrics control the next level up. Rinse, repeat, and over time and we learn about what the bigger factors are across the organization.

What I am wondering is if PowerBI can somehow respect the hiearchy and the hoped-for relationship of, say, Level 1's metrics (gathered monthly) to Level 2's metrics (gathered weekly) and so on. This would allow my Level 1 to, say, click on one of their metrics and see how Level 2's metrics are going.

Perhaps it is even possible to detect the strength of these relationships without the usual analytics I would do. Not counting on that, though. More like, "I think my metric 1.2 is related to Level 2's metrics 2.1, 2.3, and 2.7. Show me 2.1, 2.3, and 2.7 when I click on 1.2."

Is that clear? It all makes sense in my head, but not sure it makes sense outside of my head...

[SteveO1967]

This is very helpful - thank you. I'll take a look at the demo data to see if my question is answered there, and if not, I'll fake up some simplified data to post. I don't have real data yet.