Dynamic NPS which upgrade in time

Anonymous — Tue, 30 Apr 2024 07:51:10 GMT

Dear Community,
I am trying to build up a graph (or a table) showing how the NPS (Net promoter score) value for a product (called DuF) changes over time.

In my dataset I have a column showing the day and one for the exact time when the feedabck was given.
I also have a separate column for the ratings (scala from 0 to 10).

Methode	Produkt	day	time	rating
Dynamic	DuF	06.03.2024	20:03:28	5
Dynamic	DuF	12.03.2024	11:51:53	4
Dynamic	DuF	12.03.2024	14:21:10	10
Dynamic	DuF	12.03.2024	17:44:03	8
Dynamic	DuF	12.03.2024	18:44:44	5
Dynamic	DuF	13.03.2024	09:01:28	4
Dynamic	DuF	13.03.2024	09:05:23	0
Dynamic	DuF	13.03.2024	12:10:53	10
Dynamic	DuF	13.03.2024	00:17:32	10
Dynamic	DuF	14.03.2024	08:29:17	8
Dynamic	DuF	14.03.2024	10:46:58	10
Dynamic	DuF	14.03.2024	12:37:09	8
Dynamic	DuF	14.03.2024	13:10:36	9
Dynamic	DuF	14.03.2024	16:36:43	10
Dynamic	DuF	14.03.2024	17:39:22	10
Dynamic	DuF	14.03.2024	18:12:41	0
Dynamic	DuF	15.03.2024	10:24:37
Dynamic	DuF	16.03.2024	07:57:53	5
Dynamic	DuF	18.03.2024	10:28:30	8
Dynamic	DuF	18.03.2024	13:24:37	9
Dynamic	DuF	18.03.2024	19:30:32	1
Dynamic	DuF	19.03.2024	16:28:22	9
Dynamic	DuF	19.03.2024	18:06:51	2
Dynamic	DuF	20.03.2024	12:04:42	10
Dynamic	DuF	21.03.2024	06:10:17	10
Dynamic	DuF	25.03.2024	10:49:55	5
Dynamic	DuF	26.03.2024	17:47:35	8
Dynamic	DuF	27.03.2024	16:55:33	5
Dynamic	DuF	30.03.2024	23:14:52	6

In order to calculate the NPS I follow this simple formula: NPS = % Promoter - % Detractors.
Promoter are the values between 9 and 10 and detractors are the values between 0 and 7. The values between 7 and 8 are uselless (the so called "passive").

The Dax I am using is the following:

NPS_Dynamic =

VAR TotalPromoters =

CALCULATE(

COUNTROWS('DuF_NPS'),

'DuF_NPS'[rating] >= 9 && 'DuF_NPS'[rating] <= 10

)

VAR TotalDetractors =

CALCULATE(

COUNTROWS('DuF_NPS'),

'DuF_NPS'[rating] >= 0 && 'DuF_NPS'[rating] <= 6

)

VAR TotalFeedbacks = COUNTROWS('DuF_NPS')

VAR PercentagePromoters = DIVIDE(TotalPromoters, TotalFeedbacks)

VAR PercentageDetractors = DIVIDE(TotalDetractors, TotalFeedbacks)

VAR RawNPS = PercentagePromoters - PercentageDetractors

RETURN

IF(TotalFeedbacks > 0, RawNPS * 100, BLANK())

It works fine, but as you can see from the table and the line graph, each value represents the NPS calculated on the values collected on a single day. It does not update the value based on the data collected on a previous day.

So what I need is a formula that calculates the daily NPS (if there is a new value) but output the average NPS calculated on all values collected till that point in time.
For example, on day 1 I have only a value (5). In this case my NPS is -100.
On day 2, I have the following rates: 4, 10, 8, 5. On this day the NPS is -25.
But the OVERALL NPS is -40 because I need to take into account also the value (5) recoded the day before.
On day 3 we observe the following: 4, 0, 10, 10. Daily NPS is 0 but the average, which should be shown in the graph, is now -22.

For the line graph I use day on X, and NPS_Dynamic on Y.
Any help will be greatly appreciated.
Thank you very much.
Michele

Re: Dynamic NPS which upgrade in time

aduguid — Tue, 30 Apr 2024 11:20:48 GMT

NPS_Dynamic = VAR TotalPromoters = CALCULATE( COUNTROWS('DuF_NPS'), 'DuF_NPS'[rating] >= 9 && 'DuF_NPS'[rating] <= 10 ) VAR TotalDetractors = CALCULATE( COUNTROWS('DuF_NPS'), 'DuF_NPS'[rating] >= 0 && 'DuF_NPS'[rating] <= 6 ) VAR TotalFeedbacks = COUNTROWS('DuF_NPS') VAR PercentagePromoters = DIVIDE(TotalPromoters, TotalFeedbacks) VAR PercentageDetractors = DIVIDE(TotalDetractors, TotalFeedbacks) VAR RawNPS = PercentagePromoters - PercentageDetractors VAR DailyNPS = IF(TotalFeedbacks > 0, RawNPS * 100, BLANK()) VAR CumulativeNPS = AVERAGEX( FILTER( ALL('DuF_NPS'), 'DuF_NPS'[date] <= MAX('DuF_NPS'[date]) ), [NPS_Dynamic] ) RETURN IF(TotalFeedbacks > 0, DailyNPS, CumulativeNPS)

Re: Dynamic NPS which upgrade in time

Anonymous — Tue, 30 Apr 2024 11:38:03 GMT

Thank you aduguid but it seems the formula has a problem.

You have added a circular function by using NPS_Dynamic in the AVERAGEX.
And this prevents the formula to run.
Do you have another suggestion?
Thank you.

Re: Dynamic NPS which upgrade in time

aduguid — Tue, 30 Apr 2024 11:41:42 GMT

Sorry about that. I thought I'd try to solve it on my phone. I'll give it another go tomorrow.

topic Re: Dynamic NPS which upgrade in time in DAX Commands and Tips

Dynamic NPS which upgrade in time

Re: Dynamic NPS which upgrade in time

Re: Dynamic NPS which upgrade in time

Re: Dynamic NPS which upgrade in time