Re: Calculate the dynamic latest survey score base...

Anonymous · ‎02-01-2019

Hi all,

I am trying to make a dynamic report of the survey data I have based on the time range slicer.

The survey data table I connect from the database has the followings columns:

1. location ID: which is unique for each location.

2. Survey completion ID: Each location ID may have several Distinct Survey completion IDs, depending on how many times the surveys were done in each location.

3. Date: the completion date for each Survey completion ID.

4. Question: data pulled for each survey ID have at maximum 10 rows of questions. Some of the survey questions are not mandatory so some survey ID may only contain 8 rows of questions.

5. Answer: the answer to each question.

Please see below for sample data:

Location ID Survey ID Date Question Answer

1 A 2019/01/01 Q1 Yes

1 A 2019/01/01 Q2 No

1 B 2019/01/09 Q1 Yes

1 B 2019/01/09 Q2 Yes

1 B 2019/01/09 Q3 Yes

2 C 2019/01/03 Q1 Yes

2 D 2019/01/10 Q1 No

I also have another table which assigns the week information to each date.

Date Week

2019/01/01 201901

2019/01/02 201901

2019/01/03 201901

2019/01/04 201901

2019/01/05 201901

2019/01/06 201901

2019/01/07 201901

2019/01/08 201902

I created week slicer based on this table in the report page.

What I plan to summarize is the percentage of yes response (as the score) among all questions answered for each survey ID. My final goal is, I want to be able to report the average latest visit scores for each location, based on the week slicer I chose.

for example, based on the above table, if I have my slicer unfiltered, I will summarize the average score of location 1 and 2 based on the score from Survey ID B and D. If I have my slicer filtered to week "201901", the average score of location 1 and 2 I got is based on the score from Survey ID A and C, since Survey B and D were completed after week 201901.

I have created a calculated column for the latest visit in each location,

which =calculation(max(survey id), filter(location id = earlier(location id))). But in this case, the calculated column is not affected by the slicer. If I filter by week 201901, I will get blank for the average latest visit scores for location 1 & 2.

Could anybody suggest any potential solutions for the request? I would really appreciate it. Thank you so much for your help!

Ashish_Mathur · ‎02-01-2019

Hi,

You may download my PBI file from here.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Anonymous · ‎02-04-2019

Hi,

thanks for the reply.

Your answer could demonstrate the result for the weekly average score.

To clarify, when I am trying to calculate the latest visit result:

if I select 201901 in the slicer, I will get the average score of survey ID A for location 1 and survey ID C for location 2.

However, if I select both 201901 and 201902 in the slicer at the same time, I should see survey ID B for location 1 and D for location 2 since they are the latest visit result for each location based on the time range I selected.

If I create a measure by using allexcept formula to exclude the filter of the date field in the calendar table, it will not work when I calculate the score based on every row in my data table.

Please let me know if I explain it clearly.

Thanks again for the help!

Ashish_Mathur · ‎02-04-2019

Hi,

You are welcome. If you select both weeks, ID B for Location 2 should defenitely not appear because there is a No in the Answer column for that Location and Survey ID (please re-read your original post). For Location ID 1, you want only Survey ID B to appear because that has the farthest date (A should not appear). However, what result would you expect if the Date is the same for 2 survey ID's in the same Location ID.

Please illustrate very very cleary the outcome you would expect in multiple scenarios.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Anonymous · ‎02-05-2019

Hi,

Thanks so much for your help!

Based on my sample data, here is the result that I want to get:

First of all, the score for each Survey ID is :

A: 50.0%

B: 100.0%

C: 100.0%

😧 0.0%

I want to have a slicer in order to filter "YearWeek" based on the week table and to calculate the Average latest Survey result from each location based on the slicer.

Currently, I have data up to Year Week 201902.

If I do not have filter selected for Year Week, my average latest Survey Result will be based on Survey B (for location 1)and D (for location 2), therefore 50.0%

If I have my slicer filtered to Year Week 201901 and 201902, I should get the same result from above, since they are including the same time range.

If I have my slicer filter to Year Week 201901 (up to2019/01/07) only, my Average Latest Survey Result would be based on the latest survey result up to 201901, thus Survey A (for location 1) and Survey C (for location 2), which gives me 75.0% as an average

If I have multiple weeks of data, let us say, I have data for location 1 and 2 from 201901 to 201910: My Average Latest Survey Result will be calculated based on the latest survey ID in each location from the time range I selected. Ideally, the user will always need to select all the prior weeks as well as the latest Year Week that he/she wants to include up to. If he/she only selects one Year Week within the time period that I have data for, my calculation should only look into all the available Survey ID in each location in that week only, and find the latest Survey in that week only.

Please let me know if I explain my request clearly.

Thanks again for your support!