Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DataGeo
Helper I
Helper I

Multi-variable Scatter Plot

I am attempting to create a scatter plot with several variables.

 

On the Y axis, should be displayed depths (such as a sample depth) and on the y-axis I'd like to show a concentration measurement. So for each depth showing the concentration measurement as a point on the graph.

 

Now where it gets complicated is I'm working with several different measureable compounds, so I would like to display each compound as it's own trend, I've created this in excel as such:

 

Excel Graph.png

 

To make this even more complicated (and what I'm hoping powerBI will be great for) is now I have numerous different locations with this same sample data. I would like to be able to filter on each location and show the above graph so I can easily compare the trends and values. The sample depths will not all be the exact same, but will all be within a reasonable range, and with the filtering I am hoping the graph will fit the shown data.

 

As of right now I'm only able to show 1 compound at a time... I've been using the "Legend" for the location but unfortunately I can't think of a way to categorize by the compound type because each measurement depth has many compound measurements.

 

Example data set:

 

LocationSample depth (m)Compound 1Compound 2Compound 3Compound 4Compound 5Compound 6Compound 7Compound 8Compound 9Compound 10Compound 11Compound 12
A435.21.7237.9310.778.264.8897.3646.3041.3723.2912.2311.4087.63
A435.21.5541.3110.428.004.65105.9844.9439.754.0411.2910.9484.72
A438.11.1442.841.260.600.81110.805.602.043.014.663.1435.08
A441.21.0044.880.440.260.48109.250.840.640.700.710.613.04
A443.20.8943.700.410.260.44114.020.790.695.760.460.421.07
A443.51.1544.890.340.240.46111.100.680.715.710.490.441.10
B412.00.9240.850.890.650.50106.662.301.795.780.970.913.54
B413.51.5238.586.164.723.11105.2129.8122.665.489.007.7676.47
B416.00.8045.760.660.430.49122.121.441.105.170.810.772.60
B395.10.8746.830.511.311.09122.387.126.075.043.182.9746.87
B396.00.6739.730.390.340.3897.870.940.734.840.470.551.99
B396.80.8038.500.470.360.3895.350.640.474.870.380.440.55
B396.80.8239.690.350.350.3898.850.650.523.210.420.620.52
B397.80.7539.340.310.430.3993.810.610.453.630.380.740.37
C398.50.7036.830.360.210.3689.810.560.563.250.420.400.65
C508.60.7538.440.330.180.3094.490.620.623.550.480.420.53
C509.40.7739.860.400.180.3298.450.540.533.370.500.460.97
C510.40.7439.770.400.430.3494.970.730.563.670.610.641.10
C510.60.7140.260.400.420.3397.950.620.463.580.640.561.27
D511.20.6939.600.590.420.3394.900.790.593.320.720.631.27
D511.40.6938.360.520.390.3092.870.670.522.850.590.571.32
D476.60.6041.740.460.370.27100.840.730.552.880.500.551.06
D479.10.5639.240.420.350.23102.400.680.522.680.450.480.59
D390.30.5542.392.611.471.03107.3010.959.263.123.682.7220.51
D391.40.5540.722.181.180.8499.208.677.212.363.032.2218.38

 

Thanks for anyones help with this!!

1 ACCEPTED SOLUTION
drewlewis15
Solution Specialist
Solution Specialist

If I am understanding your table correctly, the numbers in each Compound Column represent the concentration that you want to display on the X axis, correct?  If so, try the following steps:

 

1. Load the table into PowerBI and edit the query.

2. Select all of your Compound columns and choose the Unpivot feature within the transform tab.

3. Add an index column

4. Close and Apply

5. Add the scatter chart visualization to your report page

6. Add Index to the Details field

7. Add Attribute to the Legend field

8. Add Value to the X Axis

9. Add Sample Depth to the Y Axis

10. Add slicers for Attribute and Location to filter the chart based on what you want to see.

 

Does this solve your problem?  Final product should look like 2017-11-27_16-45-25.jpg

View solution in original post

6 REPLIES 6
stretcharm
Memorable Member
Memorable Member

I get slightly different results to your graph but I added an index column (add column tab) to have a unqiue id for each row and then unpivoted the compound columns (select the compond columns and click unpivot from the transfor tab in the query editor). This gives then in the Attribute Field.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZZbcm0hCETncr5TlAqofN7HLFKZ/zSu0nB03+TL7PhY0LR4Pj9fv14fL2GltsZKYw88yHh/FhpjjZNa36tozjXYIPbPTlz2WIn3qsbUbO9q1Hx3Jdnzc1Dn19fH/yjV2FyBkuao4mdSV/+vkm2mCJnsyIyGb6MiQABZMD1lJ3BQk/xsqntSGk3fhGwK9eLD9DXriLm/Ff9uADCVinC6f/lBK4MyD2WlgIQQeai08hEfAibT4zRqCipmO4aBUEbFPyvQckHYIWufeWa5Q+oDAlGWOlg8DOeZJzZiUQwR9HhAFHppZIL9HJnI2V+X9BUh9Hli10xB7Apor9yQ317wJa3P2Q5AVkZ6EiuoeyEtLleH8A1Oq0hoQYC0gQFyqVyQzETdz5N07+hU4WO4nGoNj7X9RzNYoTVAFTUz1HVAvtFJxsXpkYx7R94a99CYjxh1nVuh+VFlU+o4NowL17YJE8KmhLk5cO0mjlWYG/eHSkJ4erjO6ru+GxJurtNPtzxoXJRMpY+4aKDwwwMcHWCG9GFfdlnD04I5RQXMHox55NplKfcO7hdDifW+Ir5GEsyPO7ZQPzAa8uiRgD4GZ8w0X7oOvmj13JA113LuMEYwvBktRqpT77K7csZZ2R6TvmW3xCuPEftRjz9+6CQE5Ted31UPkSJG/5qWDO1n4Gw1kYeUTDUZWib1K4+ZejJQFcH53TPJO90vWRhVjvb2RilfDCM5zl5azX6Hk4yGekgUQvKczeAwVJgF++1opfv1ODpu746bkfUQ5BFtI/wdWvVxF6k/+lYyQit/EU7PLXfmrtyyh+mtlYCh8za0g9fbgTz+OqNGl3fPbu/icLXvjJVHPBp21jCELOhwBS77xpCLMdNQ2o5ps+Ytr1uo42taXpqISoczuL0Zsrrk9cSuB3JcT0fJejbfWN5vYRZEAZl30aOZlH5BjC7TL7HyfYpEECR+ipT2tv+8M4kveZhY7Q1hWwedANZPCNenwScVTWmFBciIZ6qg/AaLMFoxA9ZQmubt+2CyKMBE/RpuR81L4jrZOtZ/VqEoIx4vlJERyvoF5i/N3P3l6+sf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Location = _t, #"Sample depth (m)" = _t, #"Compound 1" = _t, #"Compound 2" = _t, #"Compound 3" = _t, #"Compound 4" = _t, #"Compound 5" = _t, #"Compound 6" = _t, #"Compound 7" = _t, #"Compound 8" = _t, #"Compound 9" = _t, #"Compound 10" = _t, #"Compound 11" = _t, #"Compound 12" = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"Location", type text}, {"Sample depth (m)", type number}, {"Compound 1", type number}, {"Compound 2", type number}, {"Compound 3", type number}, {"Compound 4", type number}, {"Compound 5", type number}, {"Compound 6", type number}, {"Compound 7", type number}, {"Compound 8", type number}, {"Compound 9", type number}, {"Compound 10", type number}, {"Compound 11", type number}, {"Compound 12", type number}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Location", "Sample depth (m)", "Index"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

I can then use the attribute on the legend and a slicer for the location. if you only have 4 locations you could also just have four copies of the scatter each filtered by a different location.

 

Try looking these visuals as it's got more options for a scatter.

 

https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104381101?tab=Overview

https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104380762?tab=Overview

 

 

drewlewis15
Solution Specialist
Solution Specialist

If I am understanding your table correctly, the numbers in each Compound Column represent the concentration that you want to display on the X axis, correct?  If so, try the following steps:

 

1. Load the table into PowerBI and edit the query.

2. Select all of your Compound columns and choose the Unpivot feature within the transform tab.

3. Add an index column

4. Close and Apply

5. Add the scatter chart visualization to your report page

6. Add Index to the Details field

7. Add Attribute to the Legend field

8. Add Value to the X Axis

9. Add Sample Depth to the Y Axis

10. Add slicers for Attribute and Location to filter the chart based on what you want to see.

 

Does this solve your problem?  Final product should look like 2017-11-27_16-45-25.jpg

PBI_HELP1.PNG

 

Hi Experts,

I am trying to build an scatter chart of X (from K-S columns in the excel sheet image) and Y points (from T to AB columns) arranged in rows within the data file, each row corresponds to a particular object (@name()) and it varies with time (each row represents the object and the date were the X-Y points were recorded), the goal is to build a similar plot to the one shown (i.e. PCCA-001A with coordinates Xn,Yn as: Q_PR, PR/ Q_0.75, PR_0.75…Q_0, PR_0). I tried first to unpivot those X,Y points, then to use an index (1 instead of PR, 0.75, 0.5, 0.4, 0.3, 0.2, 0.1, 0) as suggested here but it did not work out.  Any possible solution it would be great. Or if it is possible to plot the scatter without unpivoting let me know. 

 

Best regards,

 

LG

Perfect, thank you!

 

Now that we've come this far, I'm wondering if we can do it 1 more...

 

 

If we filter on say compound 1 & 2 they will show up as 2 distinct colors. However, there's no easy way to discern which location we are viewing (by way of symbol or size or something like that). I tried putting the location in for color saturation but it wouldn't take.


Any idea on how to visualize/filter this way?

 

 

 

The enhanced scatter has a shape field. If you create a numerical version of the locations you can give them one of these shapes.

circle - 0
cross - 1
diamond - 2
square - 3
triangle-up - 4
triangle-down - 5
star - 6
hexagon - 7
x - 8
up arrow - 9
down arrow - 10

Excellent I will try this. The enhanced scatter seems useful but at first I was getting an error about string and aggregate data when trying to use the shape option. This was before seeing your response however so I will reformat my data and see if this works.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.