Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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:
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
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)
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.
Sorry about that. I thought I'd try to solve it on my phone. I'll give it another go tomorrow.
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
12 | |
11 | |
8 | |
6 |