Scatter plot based on pivot table data?

[maverick15]

Hello all,

I'm trying to create a scatter plot from a set of pivot table data, which I obviously can't do directly. I have a list of accounts and sales numbers by region (AMER, EMEA, APJ) and want to show them as a scatter. Works great if I copy the data static and make the chart. However, I want this as part of an interactive dashboard that I can use slicers with. I have a very ugly hack that uses a GETPIVOTDATA formula to change up the list, but the result is that every cell now has a value, so there is a line of dots across the bottom because technically there's a value there, even if it's not numeric. I've seen some posts online of ways to get around this, but the most promising one seems to have broken a few versions ago. Has anyone gotten around this?

 

[maverick15]

Forgot to note that the source data is coming from a data model. For whatever reason, columns in the source data disappear when I filter (so if I filter on AMER accounts, the columns for EMEA and APJ in the pivot go away). That's why the formulas are used the way they are.