Forum Discussion

lorenzoo's avatar
lorenzoo
Regular Visitor
2 years ago

calculation on agreement scale question

Hi there! 

I'm currently trying to create a matrix to show the agreement rate on a series of questions. My raw data looks like this: 

Customer ID Country Statement A Statement B Statement C 
1 Country 1 Somewhat agree   Strongly agree 
2 Country 2 Strongly agree Somewhat agree Strongly agree 
3 Country 2       
4 Country 3 Strongly disagree Neither agree or disagree Somewhat agree 
5 Country1 Neither agree or disagree Somewhat agree   
6 Country 3 Neither agree or disagree     
7 Country 1   Neither agree or disagree   
8 Country 3   Strongly agree Strongly agree 


The final objective I want to achieve is a matrix that can be sliced for country with a visual where I can show each statement on a column, and the % of people that either somewhat agree or strongly agree. Calculation should be as follows:
People that answered somewhat or strongly agree for the selected statement / everyone that gave at least one answer. 
In this case for example, the value for statement A should be 2/7, the value for statement B should be 3/7 and the value for statement C should be 4/7. All the values should appear as % and I have to be able to order the values for %.

Statement A X%
Statement B Y%
Statement C Z%

 

What I tried till now was unpivoting the data and counting distinct ID values as column % but the data I have is a bit overestimated compared what it should be, because when i filter the visual for strongly+somewhat agree it automatically excludes from the total the people that only gave non-agreement answers. I also tried to create a custom measure that counts the values if they are equal to strongly or somewhat agree and it works correctly, however if I try to create a correct measure for the % the number of customer IDs is also filtered for statement and my denominator varies for each statement. 
Is somebody able to help me?

Need Help

3 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Icon for Super User rankSuper User
    2 years ago

    Hello lorenzoo,

     

    Can you please try the following:

     

    1. First, ensure your data is unpivoted correctly. You can use Power Query for this.

    2. Create Measures for Agreement Rate Calculation

    Num of Agreements = COUNTROWS(
    FILTER(
        'YourTableName',
        'YourTableName'[Response] = "Somewhat agree" || 'YourTableName'[Response] = "Strongly agree"
    )
)

    Num of Respondents = CALCULATE(
    DISTINCTCOUNT('YourTableName'[Customer ID]),
    FILTER(
        'YourTableName',
        NOT(ISBLANK('YourTableName'[Response]))
    )
)

    Agreement Rate % = DIVIDE(
    [Num of Agreements],
    [Num of Respondents],
    BLANK()
) * 100
    • lorenzoo's avatar
      lorenzoo
      Regular Visitor
      2 years ago

      Hi there, thanks for your answer but unfortunately it doesn't work. The problem is that the count of total respondents, I.e. the denominator of my function also filters for rows when put in the matrix, so the calculation is wrong

  • AnalyticsWizard's avatar
    AnalyticsWizard
    Icon for Solution Supplier rankSolution Supplier
    2 years ago

    Hi lorenzoo

    Certainly! Let’s break down the steps to achieve your objective of creating a matrix that displays the agreement rate for each statement by country. We’ll calculate the percentage of people who either somewhat agree or strongly agree for each statement.

    1. Create a Custom Measure for Agreement Count:

      • First, create a new measure that counts the number of people who answered somewhat agree or strongly agree for each statement. You can use the following DAX formula for each statement (replace YourTableName with your actual table name):
        Agreement Count A =
CALCULATE(
    COUNTROWS(YourTableName),
    YourTableName[Statement A] IN {"Somewhat agree", "Strongly agree"}
)
        Repeat this for Statement B and Statement C.

    2. Create a Measure for Total Respondents:

      • Next, create a measure that calculates the total number of respondents (people who gave at least one answer):
        Total Respondents =
COUNTROWS(YourTableName)

    3. Calculate the Agreement Rate:

      • Now, create a measure for the agreement rate (percentage) for each statement:
        Agreement Rate A =
DIVIDE([Agreement Count A], [Total Respondents])
        Repeat this for Statement B and Statement C.

    4. Format as Percentage:

      • Format the newly created measures as percentages in the Modeling tab by selecting the measure and choosing the percentage format.

    5. Build Your Matrix Visual:

      • Drag the Country field to the Rows section of the matrix.
      • Place the Statement A, Statement B, and Statement C fields in the Columns section.
      • Use the corresponding agreement rate measures (e.g., Agreement Rate A) in the Values section.

    6. Order the Columns:

      • To order the columns by percentage, click on the column headers in the matrix visual.
      • In the Modeling tab, choose the Sort Ascending option for each column.

    Remember to adjust the table and column names according to your actual data.