Iterating through variable table

DAX DAX Calculations
1

Hi,

I have created a virtual table I want to use as a variable to calculate an overall score for a department head:

image
image865×515 24.2 KB

The business logic is to arrive at the overall score for a manager as depicted in the prototype below, using LM 3 as the manager here:

image

Can one help or point me in the right direction (if a video on EDNA already exists) to iterate over the variable / virtual table to calculate for each month for each row, the overall score for selected manager by:

  1. Dividing the weight of each Employee ID by the total weights of employee reporting in the same manager to obtain ‘scaled weight’
  2. Then multiply the result of step 1 above with the value of the column ‘LM team weight’ to give the column ‘Actual weight’
  3. And finally, multiply result of ‘Actual weight’ with values in column ‘Score’, row by row
  4. The final step is to then divide the result of step 3 with the total of value of column ‘Score’

There is one ‘IF’ statement logic: if an employee’s manager is the same as the selected manager, no transformation is done. that is, Step 1 is ignored & the value of ‘LM team weight’ will be 100%.

I have attached both sample PBIX file & the Excel working prototype for clarity.

Many thanks in advance
Bare

overview of prototype.xlsx (17.7 KB)

Emp Dashboard v5.pbix (166.1 KB)

2

Hi @Bare,

Think I messed up the last step but figured you could correct that :wink:
I’m having a hard time validating the numbers because the example depicted above doesn’t seem to match the sample you were working on, so I’ll leave that to you.

Anyway I hope this will bring you a step closer to a solution.

Virtual Table 4 = 
VAR vTable =
    CALCULATETABLE(
        ADDCOLUMNS(   
            SUMMARIZE(
                'Employee Score',
                'Employee Score'[Month],
                'Employee'[Employee ID],
                Employee[Manager ID]    
                        
                ),
            "Score", [Individual Score],
            "total weight", [Total Weight],
            "LM team weight", LOOKUPVALUE(DeptHeadWeights[Weight], DeptHeadWeights[Team Head], Employee[Manager ID])
            
        ),
        FILTER(
            ALL(Employee),
            PATHCONTAINS(Employee[Path], "Emp H") 
        ),
        NOT Employee[Employee ID] IN {"Emp H"})
RETURN

ADDCOLUMNS(
    ADDCOLUMNS(
        ADDCOLUMNS(
            ADDCOLUMNS(
                vTable,
                "Scaled Weight", DIVIDE( [total weight], SUMX( FILTER( vTable , [Manager ID] = EARLIER([Manager ID] ) && [Month] = EARLIER( [Month])), [total weight] ))),
            "Actual weight", [LM team weight] * [Scaled Weight] ),
        "Weighted Scores", [Actual weight] * [Score] ),
    "Result", DIVIDE( [Weighted Scores], SUMX( FILTER( vTable , [Manager ID] = EARLIER([Manager ID] ) && [Month] = EARLIER( [Month])), [Score] ))
)
1 Like
3

Hi @Melissa

Once again, massive for your assistance. Indeed, your insights & solution brought me to the solution!

I must say I once tried creating a table using variables. Didn’t work for me at the time. I was surprised it worked this time. I guess I must have done something wrong back then.

Anyway, to include step 1 per business logic, I added an IF statement at the “scaled weight” & “Actual weight” statements to check if manager ID equals manager in focus. It worked fine.

Again, thanks tons.

Cheers

4

@BrianJ
@mickeydjw

Hi Both,

Wanted to let you know the alternative to score managers irrespective of what level of management hierarchy they belong is resolved.

Input from @Melissa :slight_smile:

Thanks all!