positive response rate for large tables

[Therseb]

Dear community,

I am currently working on a Dashboard for a very large survey. Most of the questions are answered in a scale 1-5 and I need to calculate the positive response rate (# of 4, 5 / number of answers). I do have a DAX formula to calculate that, but I need to create a new measure for each question individually, which is problematic since the survey has 400+ questions. In addition to that I have another table that structures the questions and gives additional information.

data table:

Respondent id	age	gender	question 1	question 2	question 3	question 4	question 5
1	18-29	male	1	5	1	1	2
2	30-39	male	3		2	5
3	30-39	female	5	5	4	5	5
4	40-49	female		2	3		1
5	50-59	male		1	1	2	1

support table:

question category	question	question text
question category 1	question 1	question text 1
question category 1	question 2	question text 2
question category 1	question 3	question text 3
question category 2	question 4	question text 4
question category 2	question 5	question text 5

I would like to add a custom column in the support table that calculates the positive response rate for each question based on the results from the data table. I tried a few things but I just can't get it to work. Can Power BI do that?

In the end I need to filter the results by question category and demographics.

Any help is appreciated!

Thanks!

[DataInsights]

@Therseb,

I would start by unpivoting the question columns in your data table. In Power Query, select the first three columns, and select Transform --> Unpivot Other Columns. Then rename the columns as desired.

[Therseb]

@DataInsights 

I did that, now I have a table with a lot of rows that look like this:

How do I proceed?

Thanks!

[Icey]

Hi @Therseb ,

Then, create relationship between your data table and support table.

After this, you can create a column like so:

positive response rate column = 
CALCULATE ( COUNTROWS ( 'Data Table' ), 'Data Table'[answers] IN { 4, 5 } )
    / CALCULATE ( COUNTROWS ( 'Data Table' ) )

If the blank values also needed to be considered, in other words, consider the number of respondents. 

---

data table:

Respondent id	age	gender	question 1	question 2	question 3	question 4	question 5
1	18-29	male	1	5	1	1	2
2	30-39	male	3	(if needed to be considered)	2	5	(if needed to be considered)
3	30-39	female	5	5	4	5	5
4	40-49	female	(if needed to be considered)	2	3	(if needed to be considered)	1
5	50-59	male	(if needed to be considered)	1	1	2	1

---

Please try this:

positive response rate column 2 = 
CALCULATE ( COUNTROWS ( 'Data Table' ), 'Data Table'[answers] IN { 4, 5 } )
    / DISTINCTCOUNT ( 'Data Table'[Respondent id] )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Therseb]

Hi @Icey 

Thanks for the support. It works now and I created the colum successfully.

However, how does the filtering by demographics work now? Do I have to do something special? I tried to filter by gender but it doesn't do anything after I created a chart.

Thanks and best regards!

[Icey]

Hi @Therseb ,

Sorry, I ignored this request. Please try to create a measure.

positive response rate measure = 
CALCULATE ( COUNTROWS ( 'Data Table' ), 'Data Table'[answers] IN { 4, 5 } )
    / CALCULATE ( COUNTROWS ( 'Data Table' ) )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Therseb]

Hi Icey,

Thanks for the reply, sorry for the late response! This week is really busy...

Your tips really help me a lot.

I actually have one last request since I did a lot of research but just can't find a solution to it:

I need to calculate the beta score (how much does a change of 1 impact the other variable) for some questions. In excel I can just use the "slope"-function, but how do I do that with Power BI?

I need the beta score between two questions and then filter it by country.

Thanks again in advance!

[Icey · Accepted solution]

Hi @Therseb ,

Please check if this post could help you: Slope powerpivot.

The SLOPE function is based on a linear regression so that the slope is calculated as the covariance of x and y divided by the variance of x:

In DAX, the calculation for the entire table (rather than just the previous five) would look like this:

Slope = 
    VAR AvgX = CALCULATE(AVERAGE(Data[Date]), ALL(Data))
    VAR AvgY = CALCULATE(AVERAGE(Data[Flow]), ALL(Data))
    RETURN DIVIDE(SUMX(Data, (Data[Date] - AvgX) * (Data[Flow] - AvgY)),
                  SUMX(Data, (Data[Date] - AvgX) * (Data[Date] - AvgX)))

In order to get just the previous five, we need to change the filter we use when calculating the averages.

Slope = 
    VAR Filtered = TOPN(5, FILTER(Data, Data[Date] <= EARLIER(Data[Date])), Data[Date])
    VAR AvgX = CALCULATE(AVERAGE(Data[Date]), Filtered)
    VAR AvgY = CALCULATE(AVERAGE(Data[Flow]), Filtered)
    RETURN DIVIDE(SUMX(Filtered, (Data[Date] - AvgX) * (Data[Flow] - AvgY)),
                  SUMX(Filtered, (Data[Date] - AvgX) * (Data[Date] - AvgX)))

You can add a condition if you want to only return a value if it has five data points to average:

RETURN IF(COUNTROWS(Filtered) < 5, BLANK(), DIVIDE([...]))

---

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Therseb]

Hi @Icey ,

Thanks for the quick reply!

Can I actually do that with the table I created in the first step?

In the DAY I need to reference two different colums (questions), but I only have a single column for all questions now?! How would the Dax be in this case then if I want to measure the influence of question 1 on question 2?

Thanks and best regards!

[DataInsights]

@Therseb, you'll need a measure if you want to filter. Try this:

Positive Response Rate = 
VAR vNumerator =
    CALCULATE (
        COUNT ( 'Data Table'[Respondent id] ),
        FILTER ( 'Data Table', 'Data Table'[answers] IN { 4, 5 } )
    )
VAR vDenominator =
    COUNT ( 'Data Table'[Respondent id] )
VAR vResult =
    DIVIDE ( vNumerator, vDenominator )
RETURN
    vResult