Change in NPS (1-10 score) equates to an additional x% change in revenue

[Sut_Datanaut]

Hello Datanauts alike, 

I am looking to calulate the % or total change in revenue based on variance in the net promoter score of our surveys (1-10 scoring). 

So if a customer had 20k in revenue before they left a survey, and 40k revenue after their survey date, then that variance is what I am looking to aggregate. The problem I am running into is that each customers survey date is different, so i need to get an average monthly revenue pre and post survey

Ultimatley, I would like to aggregate these totals to the category in which the survey response is classified (in this case it is layout issues) and I would like to have the end metric read as follows: 

A drop in 1 Net promoter score for Layout issue responses equates to a drop in 30k in revenue from these respondants. 

Thank you, sample data is below

Account Name	Month	Year	Revenue	Survey Date	Survey Question	Net promoter score	Survey Category	Revenue Pre-Survey	Revenue Post-Survey
Customer 1	1	2022	7	2/5/2023	I really didn’t like the layout of the site	3	Layout Issues	7	0
Customer 1	2	2022	31	2/5/2023	I really didn’t like the layout of the site	3	Layout Issues	31	0
Customer 1	3	2022	20	2/5/2023	I really didn’t like the layout of the site	3	Layout Issues	20	0
Customer 1	4	2022	48	2/5/2023	I really didn’t like the layout of the site	3	Layout Issues	48	0
Customer 1	5	2022	95	2/5/2023	I really didn’t like the layout of the site	3	Layout Issues	95	0
Customer 1	6	2022	23	2/5/2023	I really didn’t like the layout of the site	3	Layout Issues	23	0
Customer 1	7	2022	69	2/5/2023	I really didn’t like the layout of the site	3	Layout Issues	69	0
Customer 1	8	2022	40	2/5/2023	I really didn’t like the layout of the site	3	Layout Issues	40	0
Customer 1	9	2022	88	2/5/2023	I really didn’t like the layout of the site	3	Layout Issues	88	0
Customer 1	10	2022	2	2/5/2023	I really didn’t like the layout of the site	3	Layout Issues	2	0
Customer 1	11	2022	40	2/5/2023	I really didn’t like the layout of the site	3	Layout Issues	40	0
Customer 1	12	2022	91	2/5/2023	I really didn’t like the layout of the site	3	Layout Issues	91	0
Customer 1	1	2023	59	2/5/2023	I really didn’t like the layout of the site	3	Layout Issues	59	0
Customer 1	2	2023	30	2/5/2023	I really didn’t like the layout of the site	3	Layout Issues	0	30
Customer 1	3	2023	87	2/5/2023	I really didn’t like the layout of the site	3	Layout Issues	0	87
Customer 1	4	2023	63	2/5/2023	I really didn’t like the layout of the site	3	Layout Issues	0	63
Customer 1	5	2023	29	2/5/2023	I really didn’t like the layout of the site	3	Layout Issues	0	29
Customer 1	6	2023	60	2/5/2023	I really didn’t like the layout of the site	3	Layout Issues	0	60
Customer 1	7	2023	47	2/5/2023	I really didn’t like the layout of the site	3	Layout Issues	0	47
Customer 1	8	2023	3	2/5/2023	I really didn’t like the layout of the site	3	Layout Issues	0	3
Customer 1	9	2023	77	2/5/2023	I really didn’t like the layout of the site	3	Layout Issues	0	77
Customer 1	10	2023	29	2/5/2023	I really didn’t like the layout of the site	3	Layout Issues	0	29
Customer 1	11	2023	31	2/5/2023	I really didn’t like the layout of the site	3	Layout Issues	0	31
Customer 1	12	2023	72	2/5/2023	I really didn’t like the layout of the site	3	Layout Issues	0	72

[v-yiruan-msft]

Hi @Sut_Datanaut ,

What's your expected result(end metric)? Do you want to get the difference between in survey and left survey? Could you please explain the backend logic base on the shared data? It would be very helpful to find out the solution. Thank you.

Best Regards

[Sut_Datanaut]

Hello, Desired output would read as follows "an increase in 1 NPS score for survey respondents lead to an average post-survey increase of 30k in revenue"

The logic base is between two tables one is the account ID/ account name with the survey output, and the second is their revenue with us over time. I have made a query to calculate pre and post revenue by survey date in the second table, and am using LOOKUPVALUE to bring those two values over by the account ID. 

Really looking for anyway to correlate the relative impact NPS has on the customer revenue behavior.

Thanks for the help! 

[v-yiruan-msft]

Hi @Sut_Datanaut ,

According to your description, it seems that it involves two tables. Could you please provide some sample data in these two tables? Is there any relationship created between them?  Do you want to find the value in another table? If yes, you can refer the following links to get it:

DAX LOOKUPVALUE and DAX RELATED Usage in Power BI (mssqltips.com)

LOOKUPVALUE (From Dataset 3) = LOOKUPVALUE ('Dataset 3'[Value], 'Dataset 3'[Category], 'Dataset 2'[Category])

Get a field value from a related table in Power BI: DAX RELATED Function Explained - RADACAD

Sub category = RELATED(DimProductSubcategory[EnglishProductSubcategoryName])

Solved: To look up value in another table between two valu... - Microsoft Fabric Community

try this code as calculated column in Table1, you may need to adjust table and column names

ProgramN =
VAR __Date = 'Table1'[Data]
VAR __InSerial = 'Table1'[IN SERIAL]
VAR __RelevantRowsTable2 =
    FILTER (
        'Table2',
        'Table2'[Date] = __Date
            && 'Table2'[Value in] <= __InSerial
            && 'Table2'[Value out] >= __InSerial
    )
RETURN
    CALCULATE ( FIRSTNONBLANK ( 'Table2'[ProgramN], TRUE ), __RelevantRowsTable2 )

Best Regards