matched pairs
1 Topic
Survey Results Matched Pairs
Hi there, I'm trying to create a measure that will calculate the average percentage change between two survey results. I've got a .pbix file with my test data here: https://www.dropbox.com/scl/fo/z8ml2mnjxvehbw8y4gahc/h?rlkey=jn3cijye52o8013sh2xmwz3eq&dl=0 The data looks like this: Enrollment ID Survey Type Gen Well Being Score Survey Date ERL01 Initial 3 01/01/2023 ERL01 Final 6 01/06/2023 ERL02 Initial 01/01/2023 ERL03 Initial 02/01/2023 ERL03 Final 4 01/06/2023 ERL02 Final 01/06/2023 ERL04 Initial 5 01/02/2023 ERL04 Initial 5 01/02/2023 ERL05 Initial 3 01/11/2022 ERL05 Final 4 01/02/2023 Its a bit messy, but I don't want to clean it up in Power Query because there's other survey question data in the same rows and I'd end up with a table for each of about 20 questions, and lots of repeated data. I need the measure to: - Calulate the average percent change between surveys, in given date period (determined by the visualisation) - It must only look at enrollments that have a score for both the initial and final surveys - Blank/null values must be removed from both inital and final surveys. - The initial survey can sit outside of an applied date filter - i.e. if a table is filtered to look at the percentage change in 2023, it can do this by including initials surveys from before that year, basically the date context will be taken only from the final survey. So in the data above there are only two valid matched pairs, ERL01 and ERL5. The average percent change therefore should be an 66.6% increase. ERL01 is a 100% increase, and ERL05 is a 33.3% increase. I've tried the following measure adapted from a similar senario I found in these forums, but its just coming up with a blank value. Percent change in Wellbeing Score (Clients with Initial and Final Survey scores) = VAR ClientsWithTwoSurveys = // Varible to identify clients that have more than one response to the General Wellbeing question FILTER ( DISTINCT ( 'Outcome Surveys'[Enrollment ID ] ), CALCULATE ( DISTINCTCOUNT ( 'Outcome Surveys'[Enrollment ID ] ) >= 2, // identifies survey responses where there are more than one response 'Outcome Surveys'[Gen Well Being Score] >= 0 // and where data exists for the General Wellbeing question ) ) VAR AvgRating1 = CALCULATE ( AVERAGE ( 'Outcome Surveys'[Gen Well Being Score] ), // finds the average wellbeing score ClientsWithTwoSurveys, // uses the variable above as a filter so that we only count completed surveys where a second survey for the same client also exists 'Outcome Surveys'[Survey Type] = "Initial", // filters out final surveys REMOVEFILTERS ( 'Calendar'[Date] ) // This should remove any filter applied to a visualisation on the date, so that an inital survey that was carried out outside of a date filter are still counted ) VAR AvgRating2 = // As above with those surveys labelled as final, but date filter should be applied in visualisations, i.e. if a year or month filter is applied this will be based on the date of the final assessment only, and inital assessment will not be filtered. CALCULATE ( AVERAGE ( 'Outcome Surveys'[Gen Well Being Score] ), ClientsWithTwoSurveys, 'Outcome Surveys'[Survey Type] = "Final" ) VAR Result = // Basic percentage change calulation DIVIDE ( AvgRating1 - AvgRating2, AvgRating1 ) RETURN Result Any help would be much appreciated. Many thanks, AdamSolved6.1KViews0likes10Comments