Survey - How to create '% of Total for Questions' measure?
Hi, I'm analysing a survey and I need to create a new measure for % of Total for Questions so that it would show me the percentage of all answers for THIS question (not ALL questions). This is what I have: and this is what I need (it's a screenshot from a youtube tutorial): I know she has a dax formula in there but I can't figure out how to adjust it to my columns. Any help would be greatly appreciated, I have spent too much time trying to solve it and I really need to move on now.
Per Cent Change Calculation in Survey Results
Hi, I'm trying to create a measure that will provide the average percentage change in wellbeing score across multiple survey results, based on what we call matched pairs. I need the measure to: - Calculate the average percent change between surveys, in given date period (determined by the visualisation, and a relationship to my calendar table - in this example a year) - It must only look at enrollments that have a score for both the initial and final surveys - a matched pair of results. Blank/null values must be removed from both initial 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. - There are sometime more than one initial or final survey under the same enrollment, in such cases I need the measure to - For initial surveys use the survey score from the first initial survey date - For final surveys use the survey score from the most recent/ latest survey date - Sometimes there are two surveys of the same type on the same day, in which case I need the measure to: - for initial surveys use the lowest score - for final surveys use the highest score Test Data: Enrollment ID Survey Type Gen Well Being Score Survey Date ERL01 Initial 3 01 January 2023 ERL01 Final 6 01 June 2023 ERL02 Initial 01 January 2023 ERL03 Initial 02 January 2023 ERL03 Final 4 01 June 2023 ERL02 Final 01 June 2023 ERL04 Initial 5 01 February 2023 ERL04 Initial 5 01 February 2023 ERL05 Initial 3 01 November 2022 ERL05 Final 4 01 February 2023 ERL01 Initial 2 20 December 2022 ERL05 Final 5 15 February 2023 ERL06 Initial 1 20 December 2022 ERL06 Initial 2 20 December 2022 ERL06 Final 6 20 March 2023 ERL06 Initial 3 15 December 2022 ERL07 Initial 2 20 June 2022 ERL07 Final 6 15 December 2022 .pbix file: https://1drv.ms/u/s!ArkjHR6LyR4yg1-jGsfmUVdv25JF?e=EstFT2 (edited link) Expected result Year Average Per Cent Change in Wellbeing 2022 200% 2023 244% Explanation: In the test data there are only four matched pairs of survey results: ERL01, ERL05, ERL06, ERL07. Where there is a score for both initial and final surveys. ERL07 is the only matched pair relating to 2022 (when the final survey was carried out). The change was 2 ->6 which is a 200% increase (difference in scores divided by original score). For 2023, the relevant scores are: Enrolment First Initial WB Score Latest Final WB Score Per Cent Change ERL01 2 6 200% ERL05 3 4 33.3% ERL06 1 6 500% Current Measure: With help from this forum (thanks Greg!), I have a starting point in terms of the DAX: Average Percent Change v2 = VAR __Valid = SELECTCOLUMNS ( FILTER ( SUMMARIZE ( FILTER ( ALL ( 'Outcome Surveys' ), [Gen Well Being Score] <> BLANK () ), [Enrollment ID ], "__Count", COUNTROWS ( 'Outcome Surveys' ) ), [__Count] >= 2 ), "__id", [Enrollment ID ] ) VAR __Table = ADDCOLUMNS ( ADDCOLUMNS ( ADDCOLUMNS ( FILTER ( 'Outcome Surveys', [Enrollment ID ] IN __Valid && [Survey Type] = "Final" ), "__Prev", VAR __id = [Enrollment ID ] VAR __Result = MINX ( FILTER ( ALL ( 'Outcome Surveys' ), [Enrollment ID ] = __id && [Survey Type] = "Initial" ), [Gen Well Being Score] ) RETURN __Result ), "__Diff", [Gen Well Being Score] - [__Prev] ), "__Percent", DIVIDE ( [__Diff], [__Prev], 0 ) ) VAR __Result = AVERAGEX ( __Table, [__Percent] ) RETURN __Result However, this version is not correctly managing the instances of multiple initial or final surveys in the way I need described above. I think I need to insert something to ensure that for each enrollment only one matched pair of results is used, as mentioned the initial score needs to remove any date filter so a score can be drawn from a previous period, so I think needs an ALL: VAR __Survey = SUMMARIZE ( 'Outcome Surveys', 'Outcome Surveys'[Enrollment ID ], "First WB Score", MINX ( TOPN ( 1, 'Outcome Surveys', [Survey Date], ASC ), 'Outcome Surveys'[Gen Well Being Score] ), "Last WB Score", MAXX ( TOPN ( 1, 'Outcome Surveys', [Survey Date], DESC ), 'Outcome Surveys'[Gen Well Being Score] ) ) Many thanks, AdamMultiple question survey
I have been trying to figure out this simple problem, but I have had no success with that. Any help would be appreciated. I have a survey with multiple questions (Q1, Q2, Q3, Q4, ...). For each question, the respondent can answer with Agree, Disagree, Strongly Agree, or Strongly Disagree. This is the Excel data Q1 Q2 Q3 Q4 Q5 Person 1 disagree disagree agree agree agree Person 2 stronglyAgree agree stronglyAgree stronglyAgree stronglyAgree Person 3 stronglyDisagree disagree agree stronglyAgree disagree Person 4 agree agree stronglyAgree stronglyAgree stronglyAgree Person 5 agree agree agree agree agree Person 6 stronglyAgree agree agree agree agree Person 7 agree disagree agree stronglyAgree agree Person 8 agree disagree disagree agree agree I need to calculate the percentage of each answer for each question and present the result below: Q1 Q2 Q3 Q4 Q5 agree 50% 50% 63% 50% 63% stronglyAgree 25% 0% 25% 50% 25% disagree 13% 50% 13% 0% 13% stronglyDisagree 13% 0% 0% 0% 0% ------------------------------------------------------------------------------------ This is the data pivoted within Power Query (one column for question and another for answer) I created the following measure, but it is not working... percentage answer = DIVIDE( CALCULATE( COUNTROWS(TABLE), ALLEXCEPT(TABLE, TABLE[field]) ), CALCULATE( COUNTROWS(TABLE), ALL(TABLE) ) ) This is a very common survey, but unfortunately, I am stuck on the calculation. Has anyone had this issue before? Thanks a lot!
