Calculate Percentage of Positive Responses

[Datagrl]

Hi PBI People!

I am working on reports for a satisfaction survey, and am having difficulty figuring out how to add a conditional percentage column to my matrix visualization.

My matrix lists each question on the left, followed by columns showing the total counts of responses for each of the likert ratings.  I need to add a column at the end showing what percentage of responses were "Agree" or "Strongly Agree" (i.e., a question receives 10 responses total, and there are 3 "Agrees" and 4 "Strongly Agrees" so I want a column at the end that shows 70%).

Can anyone help me figure out how to do this?  Thanks! 🙂

Save

[Datagrl]

Here's a snapshot of my likert matrix, if it helps:

[v-yulgu-msft · Accepted solution]

Hi @Datagrl,

From above snapshot of your matrix, suppose that your table view looks like:

To achieve your desired output, please see below steps in my test.

First, create two calculated columns in source table to display percentage value of each response type and positive response.

Count response =
CALCULATE (
    COUNT ( 'Percentage in matrix'[Response] ),
    ALLEXCEPT (
        'Percentage in matrix',
        'Percentage in matrix'[Question],
        'Percentage in matrix'[Response]
    )
)

Percentage of Positive Responses =
 (
    CALCULATE (
        COUNT ( 'Percentage in matrix'[Response] ),
        FILTER (
            ALLEXCEPT ( 'Percentage in matrix', 'Percentage in matrix'[Question] ),
            OR (
                'Percentage in matrix'[Response] = "Agree",
                'Percentage in matrix'[Response] = "Strongly Agree"
            )
        )
    )
)
    / (
        CALCULATE (
            COUNT ( 'Percentage in matrix'[Response] ),
            ALLEXCEPT ( 'Percentage in matrix', 'Percentage in matrix'[Question] )
        )
    )

Create a calculated table.

New Table1 =
SUMMARIZE (
    SELECTCOLUMNS (
        'Percentage in matrix',
        "Question", 'Percentage in matrix'[Question],
        "Response", "ZPercentage",
        "CountResponse", 'Percentage in matrix'[Percentage of Positive Responses]
    ),
    [Question],
    "Response", "ZPercentage",
    "CountResponse", MAX ( 'Percentage in matrix'[Percentage of Positive Responses] )
)

Combine source table and 'New Table1'.

New Table2 =
UNION (
    SELECTCOLUMNS (
        'Percentage in matrix',
        "Question", 'Percentage in matrix'[Question],
        "Response", 'Percentage in matrix'[Response],
        "Count Value", 'Percentage in matrix'[Count response]
    ),
    'New Table1'
)

In matrix visual, drag corresponding columns from 'New Table2'.

Best regards,
Yuliana Gu

[Datagrl]

Hi @v-yulgu-msft,

I realized that while it looks right and it calculates totals and percentages correctly, there is a functionality missing from this solution (my fault - I didn't mention the need because I didn't realize I would lose it).  I need to be able to filter the data with slicers based on survey dates.

Here is a clip of my original table, with the added columns per your instructions:

I would need to be able to somehow incorporate the Workshop End Date column in order for my matrix to be filterable by date (and other workshop info - my original matrix was connected via relationships using the date column as my unique identifier).

Would you be so kind as to help me again?

Many thanks,

Margot

[Datagrl]

Hi @v-yulgu-msft,

Sorry it took so long to get back to you.

Thanks so much for your response - this is exactly what I was looking for.  Your instructions were GREAT - clear and easy to use - and it worked perfectly. 

Thanks again! 🙂

~Datagrl