How to make dynamic charts work with functions instead sum, count, max, min etc?
Clash Royale CLAN TAG#URR8PPP
How to make dynamic charts work with functions instead sum, count, max, min etc?
everyone.
I've been trying to make a dynamic chart that works with a function to bring its values, but I don't know not even how to look for the answer on Google, all I see are thing like "see the function of your linear chart" and basic stuffs like that.
In case I haven't been clear enough:.
As you can see, my Excel is in portuguese (I'm Brazilian). The available options you can read are "Sum, Count, Average, Max, Min...". The problem, as introduced, is that I'd like to 'create' a new calculation, with an equation of my own.
When I check the VBA code for changing these values I see this:
ActiveSheet.ChartObjects("Gráfico 6").Activate
With ActiveSheet.PivotTables("Tabela dinâmica3").PivotFields( _
"Média de NPS POR CAMPANHA")
.Caption = "Soma de NPS POR CAMPANHA"
.Function = xlSum
End With
Where you see the x1Sum is exactly where I'd like it to run another function but "Sum", "Average", "Max" and stuff like that.
The function I want to create is the NPS (Net Promoter Score).
NPS = ((x/z)-(y/z))*100
Where:
x = number of promoters of my brand;
y = number of detractors of my brand;
z = total of opinions.
As you can see above, the values my charts is exctracting are not supposed to be summed or anything like that; they must follow the NPS function.
Does somebody knows how to do it? I don't know if I must code something, if there's a hidden option to do so, or even if it is impossible.
In case I haven't been clear enough, please, feel free to ask anything.
Thank you! :)
1 Answer
1
If you use a pivot table for your chart, then your options for the aggregations are limited to the pivot table aggregations. But you can create a data table that is based on the pivot table data and create formulas to calculate what you want to plot. What I don't understand is: a NPS results in a single value. Why do you need a chart for that? Or are you trying to chart multiple NPS values? Over time? Or for different services?
Build a pivot table that shows all the data with columns for promoters, detractors and neutrals, with your different time values or services in the rows. Then you can use a helper table with just the time values/services and the NPT. Calculate the NPS for each row and create a chart based on that helper table.
If you need more details, please update your question with a data sample and then post a comment.
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
Wow, I just came here to tell you that I thought of a solution to my problem and I just realized you've suggested exactly what I've done! FYI, the helper table worked perfectly. I created 4 columns with =VLOOKUP for the values in the dynamic table (pro, neu and det) and 1 for the sum of them (number of opinions). Then, the last column would calculate accordingly to these variable values that would change everytime I chose a different filter (slicer) on my Dashboard. I still have the doubt though about these charts that work with different functions... Thank you again, teylyn. :)
– Lucas Gil
Aug 14 at 14:07