Forum Discussion

mateoc15's avatar
mateoc15
Advocate II
2 years ago
Solved

Measure for one data source weighted by another

I have a data set with one record per digital survey.   There are three different surveys.

Response IDSurveySurvey ScoreDate
1Desktop Browser501/1/2024
2Mobile Browser601/1/2024
3Mobile App1001/1/2024
4Mobile App901/1/2024
5Mobile App951/1/2024

 

I have site traffic data with a number of visitors for each platform for each day.

PlatformDateVisitors
Desktop Browser 1/1/20243000
Desktop Browser 1/2/20242500
Desktop Browser 1/3/20241500
Mobile Browser 1/1/20241000
Mobile Browser 1/2/2024600
Mobile Browser 1/3/2024400
Mobile App 1/1/2024100
Mobile App 1/2/2024500
Mobile App 1/3/20244000

 

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!

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

     

     



     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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