Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a data set with one record per digital survey. There are three different surveys.
Response ID | Survey | Survey Score | Date |
1 | Desktop Browser | 50 | 1/1/2024 |
2 | Mobile Browser | 60 | 1/1/2024 |
3 | Mobile App | 100 | 1/1/2024 |
4 | Mobile App | 90 | 1/1/2024 |
5 | Mobile App | 95 | 1/1/2024 |
I have site traffic data with a number of visitors for each platform for each day.
Platform | Date | Visitors |
Desktop Browser | 1/1/2024 | 3000 |
Desktop Browser | 1/2/2024 | 2500 |
Desktop Browser | 1/3/2024 | 1500 |
Mobile Browser | 1/1/2024 | 1000 |
Mobile Browser | 1/2/2024 | 600 |
Mobile Browser | 1/3/2024 | 400 |
Mobile App | 1/1/2024 | 100 |
Mobile App | 1/2/2024 | 500 |
Mobile App | 1/3/2024 | 4000 |
The goal is to give a score for each date, but weighted based on the number of visitors to each platform. For instance, the mobile app had 3 great surveys with an average of 95. However, mobile app only made up a very small percentage of the total traffic for 1/1/2024 (mobile app traffic is 100 of the 4100 visitors that day).
So I guess my model needs to join on both date and platform/survey name, but I'm not sure what the measure would look like, not even sure where to start. Any help appreciated!
Solved! Go to Solution.
Hi @mateoc15 ,
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:
Create relationships between Survey and Platform
Create meaures
Visitor equal to date =
CALCULATE(
MAX(Visitors[Visitors]),
FILTER(
Visitors,
SELECTEDVALUE('Digital survey'[Date]) = Visitors[Date] && SELECTEDVALUE('Digital survey'[Survey]) = Visitors[Platform]
)
)
Average score =
VAR _sum =
CALCULATE(
SUM('Digital survey'[Survey Score]),
ALLEXCEPT(
'Digital survey',
'Digital survey'[Survey]
)
)
VAR _countRows =
CALCULATE(
COUNT('Digital survey'[Survey]),
ALLEXCEPT(
'Digital survey',
'Digital survey'[Survey]
)
)
RETURN
_sum/_countRows
weighted average score =
[Visitor equal to date]/
CALCULATE(
SUMX(VALUES('Digital survey'[Survey]),[Visitor equal to date]),
REMOVEFILTERS('Digital survey'[Survey])
)*[Average score]
Final output
Best regards,
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi,
Show the expected rsult clearly.
Hi @mateoc15 ,
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:
Create relationships between Survey and Platform
Create meaures
Visitor equal to date =
CALCULATE(
MAX(Visitors[Visitors]),
FILTER(
Visitors,
SELECTEDVALUE('Digital survey'[Date]) = Visitors[Date] && SELECTEDVALUE('Digital survey'[Survey]) = Visitors[Platform]
)
)
Average score =
VAR _sum =
CALCULATE(
SUM('Digital survey'[Survey Score]),
ALLEXCEPT(
'Digital survey',
'Digital survey'[Survey]
)
)
VAR _countRows =
CALCULATE(
COUNT('Digital survey'[Survey]),
ALLEXCEPT(
'Digital survey',
'Digital survey'[Survey]
)
)
RETURN
_sum/_countRows
weighted average score =
[Visitor equal to date]/
CALCULATE(
SUMX(VALUES('Digital survey'[Survey]),[Visitor equal to date]),
REMOVEFILTERS('Digital survey'[Survey])
)*[Average score]
Final output
Best regards,
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly