Forum Discussion
Scatter Plot - Need Help With Date on X-Axis and Data Summing
I am trying to create a simple scatter plot that will eventually include a large amount of data that I can use to easily spot trends. I would like to display field values for two different and distinct test types (OV / QA) over a period of time. I do not encounter any problems unless field values fall on the same date - then the values sum together. I need these three things to work together: date on X Axis, (non-manipulated) data on Y Axis, two easily distinguishable sets of data without summation.
Here are troubleshooting tips I have tried:
- Try: Put Field Value in Y Axis, Test Date in X Axis, and Test Type in Legend. Result: Delineates between data as needed to spot varying trends, but summarizes all data that occurs on same day.
- Try: Added an index to the data set and removed "summarize" on Field Values. Result: Does not work with a date in the X Axis.
- Try: Separated out the results from the data sets to add in two different types of data on the Y Axis. Result: Can only add one data set to Y Axis.
- Try: Put Values in Y Axis, Test Date in X Axis, and Index in Legend. Result: Too much data to show all of the results and every dot is a different color - no way to distinguish between two data sets.
Could someone please provide help? I also use slicers to drill down on time frames and Field Name/Table Name. Those seem to work fine. I have attached a sample set of data below.
Thank you!
3 Replies
v-piga-msftResident Rockstar
Hi jecochran ,
If it is convenient, please always show your sample data in text-tabular format in addition to (or instead of) screen captures. That allows people trying to help to readily copy the data and run a quick test, plus it increases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).
Best Regards,
Cherry
Thank you v-piga-msft! I appreciate all of the help I can get. I would like to keep the .pbix file internal if possible, but I have added a copy of the table below.
Sample ID Table Name Field Name Test Type Field Value Test Date IDField Index
OV QA 408 tbl AsphaltMixBase 1 1/2|Percent Passing QA 94.70 02/07/19 408 1 1/2|Percent Passing 150 94.70 408 tbl AsphaltMixBase 1|Percent Passing QA 91.17 02/07/19 408 1|Percent Passing 151 91.17 408 tbl AsphaltMixBase 2|Percent Passing QA 100 02/07/19 408 2|Percent Passing 152 100 409 tbl AsphaltMixBase #200|Percent Passing QA 5.44 02/07/19 409 #200|Percent Passing 153 5.44 409 tbl AsphaltMixBase #8|Percent Passing QA 31.23 02/07/19 409 #8|Percent Passing 154 31.23 409 tbl AsphaltMixBase 1 1/2|Percent Passing QA 94.26 02/07/19 409 1 1/2|Percent Passing 155 94.26 409 tbl AsphaltMixBase 1|Percent Passing QA 88.53 02/07/19 409 1|Percent Passing 156 88.53 409 tbl AsphaltMixBase 2|Percent Passing QA 100 02/07/19 409 2|Percent Passing 157 100 410 tbl AsphaltMixBase #200|Percent Passing QA 4.70 02/07/19 410 #200|Percent Passing 158 4.70 410 tbl AsphaltMixBase #8|Percent Passing QA 23.52 02/07/19 410 #8|Percent Passing 159 23.52 410 tbl AsphaltMixBase 1 1/2|Percent Passing QA 97.05 02/07/19 410 1 1/2|Percent Passing 160 97.05 410 tbl AsphaltMixBase 1|Percent Passing QA 76.47 02/07/19 410 1|Percent Passing 161 76.47 410 tbl AsphaltMixBase 2|Percent Passing QA 100 02/07/19 410 2|Percent Passing 162 100 411 tbl AsphaltMixBase #200|Percent Passing QA 6.25 02/07/19 411 #200|Percent Passing 163 6.25 411 tbl AsphaltMixBase #8|Percent Passing QA 43.75 02/07/19 411 #8|Percent Passing 164 43.75 411 tbl AsphaltMixBase 1 1/2|Percent Passing QA 94.375 02/07/19 411 1 1/2|Percent Passing 165 94.375 411 tbl AsphaltMixBase 1|Percent Passing QA 92.5 02/07/19 411 1|Percent Passing 166 92.5 411 tbl AsphaltMixBase 2|Percent Passing QA 100 02/07/19 411 2|Percent Passing 167 100 412 tbl AsphaltMixBase #200|Percent Passing OV 11.76 02/07/19 412 #200|Percent Passing 168 11.76 412 tbl AsphaltMixBase #8|Percent Passing OV 44.11 02/07/19 412 #8|Percent Passing 169 44.11 412 tbl AsphaltMixBase 1 1/2|Percent Passing OV 94.70 02/07/19 412 1 1/2|Percent Passing 170 94.70 412 tbl AsphaltMixBase 1|Percent Passing OV 82.35 02/07/19 412 1|Percent Passing 171 82.35 412 tbl AsphaltMixBase 2|Percent Passing OV 100 02/07/19 412 2|Percent Passing 172 100 - v-piga-msft
Hi jecochran ,
Thanks for your data sample firstly.
By my tests and research, I'm afraid that we cannot achieve your desired output in the bubble chart as it always show the aggregate value.
Normally, the visuals in power bi will show the aggregate value except for table visual.
Best Regards,
Cherry
