Forum Discussion
Weighted NPS Score
Are you calculating average score in PowerBI or it is coming from your data source. Reason I asked, it will be easier if we remove un-wanted rows from table using query editor:
- filter rows where score is blank
- remove score column
- get distinct rows customer|avg score|acv
once above is done, you will have only rows which we are interested to work on, add following measures:
promoter acv = Calculate(Sum(Table[ACV]), Filter(Table, Table[AvgScore]>=9)) detractor acv = Calculate(Sum(Table[ACV]), Filter(Table, Table[AvgScore]<=6)) total acv = Calculate(Sum(Table[ACV]), AllSelected(Table)) NPS = DIVIDE( promoted acv - detractor acv, total acv, 0) * 100
I thought about doing it this way but there are also comments in the surveys that we want to display in a table and if we only keep one record for each customer then we would lose the comments.
Gotcha,
- add new index column in your original table
- you can create another table as suggested in data model, reference original table and keep the original table as it is. set relationship on both tables on this index column
It will get you comments from original table.
I think that should work.
However, I calculated the average score using the following expression under the New Column feature on Power BI Desktop.
Average Customer Score = CALCULATE( AVERAGE('Table'[Survey Score]), ALLEXCEPT('Table','Table'[Customer]) )How do I recreate this in the query editor so that I can use the field when once I have removed the duplicates?
This is a DAX expression and not to use in Query Editor. Once you are done in query editor, add new MEASURE.