Scatter Plot using two datasets and a time slicer

[windynan]

Hi All - I am trying to create a scatter plot from two tables: one table stores the volume for all items by dates, the other table stores the sales amount for all item by dates. In addition, I am using a time table to filter on both tables. Like in the picture, I am trying to create a scatter plot to show volume vs sales amount for each item. I am not able to link the two tables because they both are connected to the time table .... After I put the volume and sales onto one scatter plot. It always shows as one spotted line....

Any Idea how to solve this?

[v-robertq-msft · Accepted solution]

Hi, 

According to your description, you want to add a slicer to filter the visual by category type, right?

In this way, I think you can try to create a measure instead of the calculated column to be placed into the scatter chart:

Count of sales type = COUNTX(FILTER(ALL(Category),[Item]=MAX('Sales Table'[Item])&&[Type]=SELECTEDVALUE(Category[Type])),[Type])

And you can get what you want, like this:

You can download my test pbix file below

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-robertq-msft]

Hi, 

According to your sample pbix file, I can roughly understand your requirement, I think you can create a calculated column in the sales table to be placed as the X-Axis of the chart, you can follow my steps:

1. Create a calculated column in the sales table:

Count of sales type1 = COUNTX(FILTER(ALL(Category),[Item]=EARLIER('Sales Table'[Item])),[Type])

2. Go to the scatter chart to place it like this:

3. Set the value range for the X-Axis based on the value:

And you can get what you want.

You can download my test pbix file below

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[windynan]

Thanks @v-robertq-msft, tried this. The issue with this approach is that I am not able to filter on the category type. I don't want refund records, I only want sales records. 

[v-robertq-msft]

Hi, 

According to your sample picture, I want to figure out what’s the field you placed into the Y-Axis of the scatter chart. Is it a measure or a column and what’s the table it belongs to?

I think the most proper reason for this problem is the relationships between these tables. I suggest you to post a sample pbix file with this problem without sensitive data if it’s possible so that we can help you in advance.

Scatter charts, bubble charts, and dot plot charts in Power BI

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[windynan]

Hello Robert - Here is the file. 

https://drive.google.com/drive/folders/12KdXNfep2QCGmzUhjE5HKpCFdEbyO_F3?usp=sharing