The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I am a new Power BI user. I am trying to calculate CSAT, NPS, and CES in a single report page. The challenge I have is not every survey response has a score for each of these items. Where no score is given, the cell has the text "No Reply". Specifically for NPS, I create a new column in Power BI that is type Whole Nunmber. In this column, I assing a value of -100 to any NPS response of 1-6. For 7-8, I assign a value of 0, and for 9-10 I assign a value of 100. The problem I have, is the formula results in an error because it can't handle the text from the column with the survey response score. I have tried using Power Query Editor to change the "No Reply" cells to 9999. Back in Power BI, I can calculate the NPS that ignores the cells with a 9999 value by creating a page filter. The page filter, however, affects other visualizations on the page. I tried setting a filter just on the visualization, but I am unable to edit the filter.
Is there a different/better way I could go about ignoring the cells that have the "No Reply" data in them? I don;t want to remove thoise rows in my data as they may have other data related to CSAT or CES.
Thank you.
Solved! Go to Solution.
I think I figured it out. In my input data, if I transform the No Reply and set the value to null, I can set these values in my calculated column to Blank(). This appears to work.
I think I figured it out. In my input data, if I transform the No Reply and set the value to null, I can set these values in my calculated column to Blank(). This appears to work.
@DataFun
Gald it worked for you,
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@DataFun
Can you share some sample data with the desired output to have a clear understanding of your question?
Mention whether you want a calculated column or measure.
You can either paste your data in the reply box or save it in OneDrive, Google Drive, or any other cloud-sharing platform and share the link here.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Here is an example of the data for NPS. Since the rating/score given needs to be converted, I am using a calculated column.
NPS Score (In my Original Data Set) | NPS Calc (Calculated Column) |
1 | -100 |
10 | 100 |
9 | 100 |
No Reply | 9999 |
8 | 0 |
5 | -100 |
No Reply | 9999 |
9 | 100 |
9 | 100 |
9 | 100 |
3 | -100 |
10 | 100 |
The reason I chose 9999 for those with No Reply is I received an error of mismatched type, so I changed these to a number thinking I could filter them out. For my NPS calculation I would consider the 10 other responses. What I would expect as a result is:
Average (-100, 100, 100, 0, -100, 100, 100, 100, -100, 100) = 30 for NPS.
As mentioned, in Power BI I can set a page filter that correctly calculates this, but it also affects other visualizations on the page. I tried to set a filter only on the visualization, but Power BI wouldn't allow me to set a filter at that level.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
93 | |
81 | |
63 | |
56 |
User | Count |
---|---|
247 | |
122 | |
110 | |
77 | |
72 |