Forum Discussion
Weighting calculation using field in separate table
My org has traffic to multiple sites, and different surveys for each of those sites. Let's call them Site A, B, and C (traffic); and Survey A, B, and C (responses). NPS is a calculation related basically to whether or not people would recommend the sites. Here's the calc I have for that. It's basically "number of people who like you" minus "people who don't" over "all people". (The filter for rownum is because there are potentially multiple records for the same survey response, but the same applies to what I'm looking for here - I realize it's a little goofy but it works).
NPSt (Response) =
VAR ResponseCount = COUNTAX(FILTER('Responses Task Level', [response_rownum] = 1), [response_id])
VAR PromoterCount = COUNTAX(FILTER('Responses Task Level', [recommend] >= 9 && 'Responses Task Level'[response_rownum] = 1), [recommend])
VAR DetractorCount = COUNTAX(FILTER('Responses Task Level', [recommend] <= 6 && 'Responses Task Level'[response_rownum] = 1), [recommend])
VAR Score = 0 + (PromoterCount - DetractorCount) / ResponseCount * 100
RETURN IF(NOT(ISBLANK(ResponseCount)), Score)
The requirement is to weight the calculation above by the amount of traffic, which is in a separate table 'Traffic' in my PBIX. I have created a concatenated field in both data sets to allow me to join the two in the format <YYYYMMDD><Survey/Site> That relationship is set up and active. For instance, if Site A has 100 survey responses and 1,000 visitors, but Site B has 150 responses and 500 visitors, then Site A would have twice the weight as Site B because of the amount of traffic to the site (not the number of survey responses).
In the 'Traffic' table I do have a calculation for the individual site/survey's percentage of total traffic, like so:
Visitors Month Pct of Total =
CALCULATE (
SUM ( Traffic[visitors]),
ALLEXCEPT ( Traffic, Traffic[session_start_mth_skey], Traffic[site_id] )
)
/ CALCULATE (
SUM ( Traffic[visitors]),
ALLEXCEPT ( Traffic, Traffic[session_start_mth_skey])
)
I'm struggling with where to insert the weighting in my formula, and if that's even possible. I want to weight by the 'Traffic'[visitors] field. I've tried a few different ways with no luck. If intermediate steps or measures help that's totally fine with me.
Any help appreciated! Thanks!
2 Replies
It sounds to me like you want something like this for an overall weighted score across all sites:
NPSt (Response) Weighted = SUMX ( VALUES ( Traffic[site_id] ), [NPSt (Response)] * [Visitors Month Pct of Total] )I'm not sure Traffic[site_id] is the right column in your case but this should give a general idea.
Thank you Alexis, that's an excellent start! I used exactly the calculation you provided and it does make sense. I think I'm half way there. In looking at a second chart (below) just to prove things out, I'm getting the right numbers in the tooltips. I calculated everything in Excel - % of traffic * NPSt = NPSt weighted - and it looks good. For example, the Excel D4 and F4 match the tooltip NPSt and NPSt weighted. Good stuff!
The number I'm ultimately looking for (for May 2020) is the sum of F2:F4 in the spreadsheet below, which is 28.27 (F5) and I can't seem to get to it. Maybe there's a SUM() I'm missing somewhere.
I know the NPSt (Response) to be correct - I've reconciled it with a few other existing reports - it's the sum of those weighted scores that I can't figure out. Am I missing a SUM() somewhere? In the screenshot below the Sum of visitors is also incorrect - it's about 13m and I'm expecting about 1.2m (that number should be the same regardless of NPSt calculation - and maybe that tells you something as well, that I'm doing something "bigger" wrong). I'm admittedly a Tableau guy trying to migrate a ton of reports to Power BI so you'll be seeing me around! 🙂
