So I'm trying to do a pivot table that changes the color of the data based on wether the value reaches a certain target, green if above, red if below. I the procceed to use a method similar to the one present in the following link: Create Dynamic Target Line in Excel Bar Chart
But instead I make two columns, one with the value if its above, the other if its below. And then paint each data series a different color.
So it's all well and good, but then I come across a file that does this without any of this manual labour, after checking the sheet where all of the data is in, it's formatted as a table, and when selecting this table to produce a pivot table there are extra values showing there, values these which are not actual columns in the table. What ties in to my introduction is that three of these values are: Goal, Above and Below. This person has somehow inserted a measure in this table which calculates these three things and doesn't need any ugly manual work, neatly hidden away and working exactly as the graph I made when introducing this. These three values and some more cannot go to the Legend, Filter and Axis fields, only values, while the other Items in the pivot table that are actual columns in the database can go in whichever fields.
So here comes my questions: What are these values/measures that are not actual columns in the table? Where can I find what calculation and formulas were used to make them? What is the name of this method and where can I learn more? To begin with I would like to know how to replicate this to spreasheet I'm currently working on and I believe the answers to these questions would assist me on that.
PS: I'm versed in most things in excel, but very new to naming tables, ranges and measures. I've dabbled a little with DAX and VBA, and can say that this was not done through VBA, since there are no subs in this file which have the function to do what the measures I've described above do. Thank you.
But instead I make two columns, one with the value if its above, the other if its below. And then paint each data series a different color.
So it's all well and good, but then I come across a file that does this without any of this manual labour, after checking the sheet where all of the data is in, it's formatted as a table, and when selecting this table to produce a pivot table there are extra values showing there, values these which are not actual columns in the table. What ties in to my introduction is that three of these values are: Goal, Above and Below. This person has somehow inserted a measure in this table which calculates these three things and doesn't need any ugly manual work, neatly hidden away and working exactly as the graph I made when introducing this. These three values and some more cannot go to the Legend, Filter and Axis fields, only values, while the other Items in the pivot table that are actual columns in the database can go in whichever fields.
So here comes my questions: What are these values/measures that are not actual columns in the table? Where can I find what calculation and formulas were used to make them? What is the name of this method and where can I learn more? To begin with I would like to know how to replicate this to spreasheet I'm currently working on and I believe the answers to these questions would assist me on that.
PS: I'm versed in most things in excel, but very new to naming tables, ranges and measures. I've dabbled a little with DAX and VBA, and can say that this was not done through VBA, since there are no subs in this file which have the function to do what the measures I've described above do. Thank you.
