Measure for one data source weighted by another

[mateoc15]

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!

[Ashish_Mathur]

Hi,

Show the expected rsult clearly.

[v-heq-msft · Accepted 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