Formula Generator | DAX

DAX Formulas for Survey Data Analysis

This document outlines DAX formulas to compute average scores from customer and employee survey data, enabling effective analysis of responses through aggregated metrics.

Prompt

Customer Survey Data:
Question	Poor (1)	Fair (2)	Good (3)
Quality of Food and Drinks	2	3	1
Service Speed	1	2	3
Cleanliness of the Venue	0	2	4
Friendliness of Staff	2	1	3
Value for Money	3	2	1
Overall Experience	1	2	3
Analysis:
•	Quality of Food and Drinks:
o	Poor (1): 2 customers
o	Fair (2): 3 customers
o	Good (3): 1 customer
•	Service Speed:
o	Poor (1): 1 customer
o	Fair (2): 2 customers
o	Good (3): 3 customers
            
Employee Survey Data:
Question	Poor (1)	Fair (2)	Good (3)
Working Conditions	3	2	1
Communication with Management	2	3	1
Fairness in Workload	1	3	2
Training and Development	2	2	2
Job Satisfaction	1	4	1
Recognition of Efforts	4	1	1
Analysis:
•	Working Conditions:
o	Poor (1): 1 employee
o	Fair (2): 2 employees
o	Good (3): 3 employees
•	Career Advancement Opportunities:
o	Poor (1): 3 employees
o	Fair (2): 2 employees
o	Good (3): 1 employee

Answer

DAX Formula for Customer and Employee Survey Data Analysis

Overview

This document provides DAX formulas to calculate aggregated metrics from customer and employee survey data. The aim is to analyze the surveys by converting counts of responses into meaningful statistics.

Customer Surveys

DAX Formula

To calculate the average score given by customers for various questions, you can use the following DAX formula:

Average_Customer_Score = 
VAR Total_Responses = 
    SUM(CustomerSurvey[Poor (1)]) + SUM(CustomerSurvey[Fair (2)]) + SUM(CustomerSurvey[Good (3)])
VAR Weighted_Score = 
    (SUM(CustomerSurvey[Poor (1)]) * 1) + (SUM(CustomerSurvey[Fair (2)]) * 2) + (SUM(CustomerSurvey[Good (3)]) * 3)
RETURN
IF(Total_Responses > 0, Weighted_Score / Total_Responses, BLANK())

Explanation

Variables:
- Total_Responses: Sums all response categories to get the total number of responses for the specific question.
- Weighted_Score: Calculates the total score by multiplying each response category by its respective value (1 for Poor, 2 for Fair, 3 for Good).
Return Value:
- Uses an IF statement to handle cases where no responses are present. If Total_Responses is greater than zero, it divides Weighted_Score by Total_Responses to produce the average score.

Practical Illustration

For example, if we analyze the "Quality of Food and Drinks":

Poor: 2 customers = 2 * 1 = 2
Fair: 3 customers = 3 * 2 = 6
Good: 1 customer = 1 * 3 = 3
Total Responses = 2 + 3 + 1 = 6
Weighted Score = 2 + 6 + 3 = 11
Average Score = 11 / 6 = 1.83 (approx).

Employee Surveys

DAX Formula

To calculate the average score for various questions from the employee surveys, the formula is similar:

Average_Employee_Score = 
VAR Total_Responses = 
    SUM(EmployeeSurvey[Poor (1)]) + SUM(EmployeeSurvey[Fair (2)]) + SUM(EmployeeSurvey[Good (3)])
VAR Weighted_Score = 
    (SUM(EmployeeSurvey[Poor (1)]) * 1) + (SUM(EmployeeSurvey[Fair (2)]) * 2) + (SUM(EmployeeSurvey[Good (3)]) * 3)
RETURN
IF(Total_Responses > 0, Weighted_Score / Total_Responses, BLANK())

Explanation

This formula functions similarly to the customer survey formula, designed to compute the average score from the employee responses.

Practical Illustration

For example, analyzing "Working Conditions":

Poor: 3 employees = 3 * 1 = 3
Fair: 2 employees = 2 * 2 = 4
Good: 1 employee = 1 * 3 = 3
Total Responses = 3 + 2 + 1 = 6
Weighted Score = 3 + 4 + 3 = 10
Average Score = 10 / 6 = 1.67 (approx).

Conclusion

The DAX formulas provided facilitate the analysis of customer and employee surveys, allowing for an understanding of average ratings in various categories. Utilizing these formulas, you can effectively interpret survey data and utilize it for decision-making.

To further refine your DAX skills, consider exploring resources available on the Enterprise DNA Platform.

Description

This document outlines DAX formulas to compute average scores from customer and employee survey data, enabling effective analysis of responses through aggregated metrics.