6

After reading this thread on the official forums I still didn't see how to plot columns without aggregation, and that thread did not offer any working solution.

Is there a way in MS Power BI Desktop to simply plot one or more columns without any aggregation?

Imagine I just have a simple table, imported from csv, that has numerical values in two columns, and I want to plot those individual values in a scatter plot, which is one of the most elementary tasks possible and is simply done e.g. in Excel.

Is this not possible in Power BI Desktop?

Jeroen
  • 60,696
  • 40
  • 206
  • 339
Khris
  • 3,132
  • 3
  • 34
  • 54

1 Answers1

5

To my surprise, this was certainly not trivial...

The only solution / workaround I found so far feels hacky, and probably doesn't scale up for too many datapoints. But here it is nonetheless.

For simple, small, whole-number datasets...

Suppose this dataset:

X;Y
1;2
1;2
1;4
3;4
4;1
1;2
4;1
2;1

Import that CSV and add a calculated column:

Label = "(" & [X] & ";" & [Y] & ")"

Now create the scatter plot with:

  • Legend set to Label
  • X Axis set to Average of X
  • Y Axis set to Average of Y

And you'll get this:

scatter plot for the above

Not optimal, but at least it's a start for smaller datasets.

For simple, small, fractional number datasets...

The same process as above, and it'll look like this:

fractional datasets

The legend is starting to break down and is probably best removed.

For larger datasets...

Suppose you have an Excel sheet with two columns X and Y, both with the formula =RAND() * 5. Copy paste this row to e.g. 500 rows, so you have many data points.

Load it up in PowerBI. Add the Label column like above. Add a scatter plot and drag Label to Details, and set X and Y to their respective axis. Here's the result:

larger plot

It works pretty decently, I guess.

Jeroen
  • 60,696
  • 40
  • 206
  • 339
  • Thanks for the reply, I will try this out on Monday. – Khris Feb 25 '17 at 17:11
  • It worked, but it is a workaround and the mere fact that you have to use a workaround to do such a basic thing speaks against using this software at all. What were they thinking? – Khris Feb 27 '17 at 06:33
  • 2
    @Khris - Power BI is a data analysis tool. The primary purpose is to take raw data & aggregate it in different ways to answer questions (how many sales by year, how many customers by location, etc). What you're trying to do, on the other hand, is more like a graphing tool (plotting points on a chart without aggregation). You *can* break raw data down to its lowest level in Power BI (e.g. how many sales by SaleID), but trying to render large datasets unaggregated isn't what Power BI is good at, or designed for. – Leonard Feb 27 '17 at 19:29
  • 1
    Put another way, Power BI is designed as an interactive tool. Charts in Power BI expect to provide context (such as telling you what the dot represents when you hover over it - which customer, or which sale, or which source system ID field it represents). An X;Y coordinate has no such context. While Power BI could make something up - which is what Jeroen's answer does - it's still better to provide that context yourself. That way, if you see an outlier, you can go "oh, observation 3437 looks weird, let's check it out" rather than "coordinate {3;4} looks odd, but what observation ID is {3;4}?" – Leonard Feb 27 '17 at 21:12
  • 2
    Raw data is an extremely important part of any analysis. For many cases, the first thing that you want to do is plot the raw un-aggregated data. Power BI is a non-started here. – Doug Kimzey Sep 28 '19 at 15:16
  • @Jeroen do you have any idea to fix this? https://stackoverflow.com/questions/66851983/suitable-graph-to-visualize-shift-wise-ordinal-data-using-powerbi – johnson Mar 29 '21 at 10:35