Search code examples
powerbidaxvisualizationpowerbi-desktopmeasure

How to plot duplicate values on a multiple lines chart without summing them up?


Creating this question per request from @davidebacci here:

How to combine multipe scatter charts into one in Power BI?

I want to create a chart exactly like the one I posted on the above question.

But I need it to allow values to repeat.

So the dataset for that chart would be this new one:

Line 1 Xval | Line1 YVal

10 | 2

11 | 3

12 | 4

13 | 5

14 | 6

15 | 7

15 | 10 (new value)

Line 2 Xval | Line2 YVal

10 | 1

11 | 2

12 | 3

13 | 4

14 | 5

15 | 6

15 | 6 (new value)

dot Xval | dot Yval

12 | 5

I just added two new coordinates (15, 10) for Line 1 (which duplicates the number 15 on Xval) and another (15, 6) coordinate for Line 2 (duplicating that exact same coordinate that already existed).

The solution on that post doesn't allow duplicate values as it interprets Xval as an Id for relationship, while in reality they should be just (x, y) coordinates that can repeat themselves sometimes.

And if the same coordinate repeats, it should just plot that same coordinate again in the same place instead of suming them up with the other duplicate value.

For instance, the two (15, 6) coordinates on Line 2 should result in one (15, 6) ploted on the chart, not (15, 12) which would be the sum of the y value of each of them.

So, the resulting chart for these values should be like this (as I have in excel):

enter image description here


Solution

  • I think the line 2 (15,6) is fine as you can write a measure that just plots the max (i.e. it will select one of the duplicates).

    What do you want to happen for the line 1 (15,10). Do you want 15,10 and 15,7 to both be plotted? How would a line connect these? You need some kind of aggregation to plot on a line chart in PowerBI.

    enter image description here

    myY = MAX(Append1[y]) 
    

    To plot individual points, you can switch to a scatter chart but then you can't have your lines.

    enter image description here