Scatterplot - I don't want the Y-axis measure aggregating

[MarkD1722]

I have a couple scatterplots with a few attributes, and my y-axis is a measure that results in a number.  For each attribute, it is more or less assured that there will always be points that will have the same x-axis value...mostly because "zero" is a starting value.  That said, when plotting multiple points that share the same exact x-value (e.g., 0), the y-axis values (which are different) aggregate rather than display individually (see image below).  In my case, my measure uses AVERAGEX, so I am getting an average.   In the image below, I have 5 rows all of which should calculate a different y-axis value...but you can see that each attribute has less than 5 points plotted because some have equal x-axis values.

I tried to use a Calculated Column instead, but calculated columns work differently than measures.  I have a dynamic (selected slicer) value that is used in the calculation, and I couldn't get it to work (but I am open to trying that again if I understood how to modify the DAX).

I also tried to find other posts that offered a couple solutions, but nothing that I found appeared to solve this.  Feel free to point me in the direction of such posts if you think the solution lies there.  Thanks in advance for any help you can provide.

[MarkD1722 · Accepted solution]

I was able to work out the solution.  It came down to understanding the visual a bit better.  Also, if the answer lie in the several post I looked at, I completely missed it.  Basically, I was able to make measures for BOTH the x-axis and the y-axis.  Then, I added a column value (i.e., the primary key) in the "Values" box.  This plotted a dot for each row, at the coordinates of the two measures.  This addition allowed the aggregation to work for EACH ROW rather than each unique result (which could involve more than one row--e.g., all rows where the measured equalled zero) in the x-axis measure.  In previous attempts to use that Values box, with only one axis defined or only one measure in play, it would not work, and I had ignored it until this last attempt.  The legend was used to separate the different values which were independent raw values in a narrow table.

[v-junyant-msft]

Hi @MarkD1722 ,

Do you want to display these overlapping points separately? If so, you must have a field that can be used to distinguish the different points and put this field into the visual object to achieve this. It is impossible to achieve this by simply modifying your measure or converting the measure to a calculated column.
You did not provide your data or your DAX, so I cannot help you find a suitable solution. I can only tell you a solution idea. If possible, please provide your pbix or sample data, thank you!

Best Regards,
Dino Tao

[MarkD1722]

Thanks, Dino.

The DAX is below... it's a measure at this point.  I tried the DAX in a calculated column, but realized that there is no way to get to work it dynamically with the selectable currency.   For context, for each data point, I am trying to display the project cost (y-axis) versus a change in length of the project (x-axis).  That project cost value, however, is displayed in the currency of your choice via a slicer using a dynamic currency exchange algorithm (in the variables section of the DAX).   I have to use an aggregation function in the DAX, so I chose AVERAGEX aribtarily.

So, while I would expect that some points in the scatterplot could overlap, they should only overlap when BOTH the change in length of project (x-axis) and project cost (y-axis) are exactly the same.  In this visual, however, the averaging occurs for the y-axis values when the x-axis value is the same.

The DAX is:

VAR Select_Currency = SELECTEDVALUE(Currency_Exchange_Rate[Currency]) --for selecting the currency

VAR Proj_Yr_Month = MAXX('PROJECT LIST', 'PROJECT LIST'[Approve_New_Year_Month])

VAR Resulting_Exch_Rate = CALCULATE(MAX( 'currency_exchange_rate_table[Exchange_Rate])

                                    , REMOVEFILTERS('Calendar Table)

                                    , 'currency_exchange_rate_table'[Currency] = Select_Currency

                                    , 'currency_exchange_rate_table'[NewExchRateYearMonth] = Proj_Yr_Month)

RETURN

CALCULATE(

    AVERAGEX('PROJECT LIST', ''PROJECT LIST'[Project Amount USD])

    , REMOVEFILTERS('Calendar Table'[Year]))

* Resulting_Exch_Rate  --applies the currency exchange rate

Thanks.

Mark