Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
michelebu
New Member

Dynamic NPS which upgrade in time

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.

 

michelebu_0-1714461901309.png


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).

MethodeProduktdaytimerating
DynamicDuF06.03.202420:03:285
DynamicDuF12.03.202411:51:534
DynamicDuF12.03.202414:21:1010
DynamicDuF12.03.202417:44:038
DynamicDuF12.03.202418:44:445
DynamicDuF13.03.202409:01:284
DynamicDuF13.03.202409:05:230
DynamicDuF13.03.202412:10:5310
DynamicDuF13.03.202400:17:3210
DynamicDuF14.03.202408:29:178
DynamicDuF14.03.202410:46:5810
DynamicDuF14.03.202412:37:098
DynamicDuF14.03.202413:10:369
DynamicDuF14.03.202416:36:4310
DynamicDuF14.03.202417:39:2210
DynamicDuF14.03.202418:12:410
DynamicDuF15.03.202410:24:37 
DynamicDuF16.03.202407:57:535
DynamicDuF18.03.202410:28:308
DynamicDuF18.03.202413:24:379
DynamicDuF18.03.202419:30:321
DynamicDuF19.03.202416:28:229
DynamicDuF19.03.202418:06:512
DynamicDuF20.03.202412:04:4210
DynamicDuF21.03.202406:10:1710
DynamicDuF25.03.202410:49:555
DynamicDuF26.03.202417:47:358
DynamicDuF27.03.202416:55:335
DynamicDuF30.03.202423:14:526

 

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

3 REPLIES 3
aduguid
Super User
Super User

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.