First, you need to filter out the rows in Table 2 that are of type "CSAT". Then, you'll want to count the number of occurrences of each score for each skill. Once you have these counts, you can multiply them by their respective weights to get the weighted score for each skill. Finally, you'll sum up these weighted scores and divide by the total number of CSAT responses to get the average CSAT per skill.
Here's a DAX formula that should help you achieve this:
Average CSAT per Skill =
VAR CSATTable = FILTER(Table2, Table2[Type] = "CSAT")
VAR Score5Count = COUNTROWS(FILTER(CSATTable, CSATTable[Score] = 5))
VAR Score4Count = COUNTROWS(FILTER(CSATTable, CSATTable[Score] = 4))
VAR Score3Count = COUNTROWS(FILTER(CSATTable, CSATTable[Score] = 3))
VAR Score2Count = COUNTROWS(FILTER(CSATTable, CSATTable[Score] = 2))
VAR Score1Count = COUNTROWS(FILTER(CSATTable, CSATTable[Score] = 1))
VAR WeightedScore =
(Score5Count * 1) +
(Score4Count * 0.75) +
(Score3Count * 0.5) +
(Score2Count * 0.25) +
(Score1Count * 0)
VAR TotalCSATResponses = Score5Count + Score4Count + Score3Count + Score2Count + Score1Count
RETURN
IF(TotalCSATResponses = 0, BLANK(), WeightedScore / TotalCSATResponses)
This formula first creates a table with only the CSAT responses. It then counts the number of each score and calculates the weighted score. Finally, it divides the weighted score by the total number of CSAT responses to get the average CSAT per skill.
