Calculate team head performance

Bare · February 23, 2020, 9:05am

Absolutely clear - thanks for systematic explanations!

I am a firm believer in virtual tables myself since VAR was first released. But I always say, " we learn something new everyday!". Your explanation on point 2 nailed. It makes absolutely sense & made it work (thus saving me the resource intense approach of materializing the virtual tables)

I did a bit of online research and you are spot on with your use of AVERAGEX - the general ‘agreement’ is that one can access columns within virtual tables using only the X family of DAX functions. Aggregators won’t work!

Only question I may have is if you had a chance to look at what I am aiming to achieve overall with this post?

@mickeydjw is already helping me out as I also try to figure out a solution (of course with thanks to your help on the virtual table) as well. Don’t know if you maybe have insights as well?

Cheers

BrianJ · February 23, 2020, 2:40pm

@Bare,

I learned this lesson the hard way. I had a number of attempted solutions in the past where I’d build a complex virtual table and then have no way to call the column I needed, since you need the function you’re using to have an explicit table call to access the virtual table. X functions all work well, as do TOPN, COUNTROWS, etc. - anywhere you can “activate” the virtual table directly.

I’ve been reading this thread, but haven’t dived into the details, since I didn’t want to step on @mickeydjw‘s work on the solution or duplicate efforts. Just noticed the DAX virtual table problems and figured I’d send that along. But if you and @mickeydjw would like another set of eyes on the problem, I’d be happy to do that.

Brian

Bare · February 23, 2020, 4:54pm

@BrianJ

I’d very appreciate any set of eyes on this ‘special’ case I have on me

I’ve been stuck on this for quite a while and hoping help will come from EDNA. @mickeydjw has taken particular interest but please feel free to jump in. So far I’ve gotten as far the virtual table for each pathitemreverse level to get team score. Just trying anything here.

And like you, I’m learning the hard way with this report challenge

Bare

BrianJ · February 23, 2020, 5:01pm

@Bare,

Sure – glad to pitch in and see if I can help crack this one. Two things that would be a huge help - can you please post your most recent PBIX file incorporating the progress you’ve made to date, and provide a mockup of the final result you’d like to see.

Thanks.

Brian

Bare · February 23, 2020, 5:43pm

Thanks @BrianJ

Attached is the most recent pbix file. But first the mockup view:

Each manager’s team score takes as input the total score from their employee. The total score is a weighted average of an individual & team score for each employee (including managers!). for employees with no teams (Team L0 in the employee table of pbix), easy: individual is same as total score. It get’s trick once from Team L1 through L3. Two challenges:

How do I calculate Emp B’s Team score with Total score from Emps C (this one is easy as Emp C is Team L0) & D with Emp D’s Team score that feeds into its Total score relying on the Total score of Emps F & G
Notice Emp B spans Team L1 & Team L2. How do I ensure Team score calculations only focuses on direct reports (in this case Emps C & D)? In case Team L1 would be relevant for Emp B

I hope this clarifies the work @mickeydjw & I are both looking at. Otherwise do let me know. I have also attached the excel prototype for the formulas on calculating the team & total scores

Many thanks again

Emp Dashboard v3.pbix (166.5 KB)
overview of prototype.xlsx (13.7 KB)

BrianJ · February 24, 2020, 5:38pm

@Bare,

Thanks very much for the detailed explanation and all the associated files. I’m starting to dig into this one in-depth - it’s a really interesting problem. @Melissa and I are also tag teaming on a complex support request for @benwann1, and I’ve got a number of pressing work deadlines this week, so I may not be as prompt as usual in getting back with a solution on this, but will keep you posted on my progress.

Brian

Bare · February 25, 2020, 6:40am

Hi @BrianJ

Thanks for having a look. No worries re response timing. Indeed, it is a challenge that has got me stumped. I’ve not progressed beyond the updated pbix file I sent you (each time I think I made a progress I go back to square one!)

Much appreciate
Bare

mickeydjw · February 25, 2020, 8:39pm

I’m still tinkering as well as changing jobs, so don’t think I’ve forgotten about you.

Bare · February 26, 2020, 8:57pm

@mickeydjw

I never thought you forgot - was more thinking in line of the tinkering as that’s what this case has got me doing in the past week

I wish you all the best in your new job though

I haven’t made further progress from my last update on this case - taking a step back to see what eludes me in the imlementation

EnterpriseDNA · March 2, 2020, 2:23am

Due to the length by which this post has been active, we are tagging it as Solved. For further questions related to this post, please make a new thread. You may check this how-to guide for reference - How To Use The Enterprise DNA Support Forum

BrianJ · March 2, 2020, 3:23am

@EnterpriseDNA,

I’m still actively working a solution on this one. Is it possible to keep it open a bit longer, to keep continuity on this discussion? Thanks.

Brian

Bare · March 3, 2020, 7:20pm

@EnterpriseDNA

Hi Team, I’d like to keep this still open as work is still ongoing to find a solution.

How do I ‘unsolve’ it?

BrianJ · March 3, 2020, 7:38pm

@Bare,

You should be able to go into post #20, click on the three dots next to Reply and uncheck the solution box.

FYI – I’ve got the whole evening blocked off to work on this if needed. Hope to have significant progress for you by tomorrow.

Brian

Bare · March 3, 2020, 8:14pm

thanks @BrianJ - unchecked the solution box

Much appreciate your time & effort. Keeping fingers crossed & looking forward to learning a trick or 2

BrianJ · March 4, 2020, 6:39am

@Bare,

Incredibly fun and challenging problem. Making progress, but have a question - can I always count on the maximum number of levels being 4, or do I need to account for that being dynamic as well?

Thanks.

Brian

Bare · March 4, 2020, 7:55am

@BrianJ

number of levels to account for dynamism. Some managers will be only 3, while some may go up to 7!

One thing I noticed when I worked on it, also, is that a manager could have employees with mixed levels…for example a manager could have an employee with no management role reporting to him/her AND as well another employee with management role. In the prototype excel mockup, as a practical example, we could have a scenario where Person 2 reports to LM 3 instead of reporting to LM 1

BrianJ · March 5, 2020, 5:06am

@Bare,

OK, 7 is the number I needed (frankly, I was hoping it would be 4, but you have to play the cards you’re dealt, not the cards you want…). If you have Russo and Ferrari’s “DAX Patterns”, the last chapter on Parent-Child Hierarchies is very useful, and in it they state “to create the right number of calculated columns, you must know in advance the maximum depth of the hierarchy. Otherwise you have to estimate it because the number cannot change dynamically”.

I spent last night sketching this problem out and rubber ducking potential approaches, which convinced me of two things:

we are going to have to eat this elephant in small pieces. As you’ve identified, the self scoring portion is easy. However, jumping from that straight to the full team calculation is too big and complex a piece to digest. The approach I plan to take is to start with a two level hierarchy (A,B and H), predicated on a reshaped supporting table that defines the direct reporting relationships:

This type of supporting table should make writing the DAX much easier. Then once we’ve validated that we’ve got a two level hierarchy working, we keep building out until we get to level 7.

currently, we have DAX doing too much of the work. Given the static nature of the hierarchical relationships, a lot of this task I think can and will be handled ultimately via Power Query. In order to start doing that, can you please upload the “20190817 Sample data.xlsx” file?

While so far I’ve been working primarily at just a conceptual level, I did mark the date table, and change the DAX in the Team L0 calculated column to:

Team L0 = 

    IF(Employee[Head role] = "n",
    Employee[Employee ID],
    BLANK()
    )

Rather than waiting until I have a full 7–level solution, I will post progress as I go so that you, @mickeydjw and I can work together in moving this one forward.

Brian

Bare · March 5, 2020, 7:42am

Hi @BrianJ

Indeed this is an interesting approach you bring up. I’m all excited. Yes I do have the DAX Patterns and will review that portion. What intrigues me is the use of Power Query as you’ve mentioned: I doubt I ever come across hierarchies built in PQ (I’m usually one to go as far as possible in PQ before handing over to DAX for modelling).

Thanks for the learning opportunity.

Attached is the sample data file.

20190817 Sample data.xlsx (20.7 KB)

BrianJ · March 5, 2020, 1:21pm

@Bare,

Thanks very much for promptly posting the data file. In rereading my previous message, I can see that it was unclear. Your employee table and the created hierarchies are perfect - exactly the form that Russo and Ferrari recommend.What I was referring to was the supporting tables that I think we will need to create in order to iterate over to develop the team scores. Because these hierarchies will be static (in terms of not changing during a single session), we can materialize these as physical tables using Power Query, rather than as virtual table variables in DAX measures. This will definitely make our measures less complex and the overall analysis easier to debug.

I haven’t worked much with PATH functions in the past, so this will be a good learning experience for both of us in different ways.

More to follow soon…

Brian

P.S. I came across this video at lunch today. I’ve never seen this done in Power Query either, but some interesting techniques here:

EnterpriseDNA · March 10, 2020, 10:27pm

Hi @Bare, we’ve noticed that no response has been received from you since the 5th of March. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!