Need to use Power Pivot and slicers to show all unique item prices by month in a chart

[thalamus]

Hello,

I currently query large amounts of transaction purchase order data which basically shows the item price and month it was purchased.

I want to be able to filter to an individual item ID with a slicer and have the chart show all unique prices for that item by month. The issue is that there are sometimes multiple prices for the same item in the same month, and I don't believe excel is letting me use a scatter plot with a pivot chart.

I currently have the DAX formulas setup to uniquely identify the max item price per item and chart that by month. It's better than nothing but I was wondering if there was a way to show ALL unique item prices by month on the chart.

I would love to insert my file but embarrassingly, I am new to the forum and do not see an insert file button.

Thank you very much for the help,

Marc

 

[ImkeF]

Hi Marc,
you should be able to show data from your PP Model also in table form, if they come from just one table (which seems to be the case here). You can apply slicers as well. There is also no need to remove duplicates if you want to show it as a scatter chart.

Go to "Data" - "Existing Connections" - "Tables" - then below "this workbook data model" should be displayed all tables that sit in the datamodel seperately. Select the one you need, choose open and the default selection should be "table" - accept and there you have your pp table in as an Excel table which has the right format for you scatter chart.
Insert the slicer (table tools) and take date and price as input for the chart.

BR, Imke

 

[scottsen]

I typically recommend just adding a link to your workbook via dropbox,onedrive,googledrive whatever.

Out of curiosity, can you get what you want in a pivot table (ignoring any charty-weirdness) ?

 

[thalamus]

Imke,

I tried doing that, but for some reason was unable to get the months to display properly on the scatter plot. Can you please be more specific on how to do this?

Scottsen - here is a link to my file so you guys can see the data layout. The issue with a normal pivot table is it will sum all my item prices, not show me unique ones. I don't know of a solution for this in a regular pivot table.

https://www.dropbox.com/s/fw6me60707l620t/Powerpivot example.xlsx?dl=0

 

[scottsen]

So, if i just put month and price on rows... and slice by product, I get this:



From here.. where are you hoping to go?

 

[thalamus]

I've been to that point, but my problem was showing all of that on a chart... Scatter plot seemed like the only viable option seeing as how there were multiple item prices per month. However, I cannot use a scatter plot from a pivot table. I think Imke touched on what to do above but I couldn't get the months to show on the axis properly.

 

[ImkeF]

Hi Marc,
the specific thing with scatter charts is that you can only show numbers - also on the x-axis. So you need to use the months numbers instead of their names.
https://support.office.com/en-ie/ar...ne-chart-4570a80f-599a-4d6b-a155-104a9018b86e

BR, Imke