Scatter Plot - Not Summarizing a Whole Number for Y-Axis

[Anonymous]

Hi all,

This is an extension on another graph I asked about before (https://community.powerbi.com/t5/Desktop/Graph-Visualization-Help-Showing-only-Decreases-and-Equal-t...).

Let's take the same dataset from above (with some minor tweaking now):

Scenario	Group	Customer ID	Old Rating	New Rating	Rating Difference	Value ($)
1	Automotive	BOB	RT-3	RT-5	2	1000
1	Automotive	BOB	RT-3	RT-6	3	2000
1	Agriculture	TED	RT-4	RT-2	-2	5000
1	Agriculture	JOHN	RT-2	RT-2	0	1333
1	Finance	GEORGE	RT-5	RT-2	-3	9990
1	Finance	GEORGE	RT-5	RT-10	5	1222
1	Travel	VICK	RT-1	RT-1	0	5232
1	Travel	TAMMY	RT-5	RT-2	-3	7131
2	Automotive	BOB	RT-3	RT-5	2	1000
2	Automotive	BOB	RT-3	RT-6	3	2000
2	Agriculture	TED	RT-4	RT-2	-2	5000
2	Agriculture	JOHN	RT-2	RT-2	0	1333
2	Finance	GEORGE	RT-5	RT-2	-3	9990
2	Finance	GEORGE	RT-5	RT-10	5	1222
2	Travel	VICK	RT-1	RT-1	0	5232
2	Travel	TAMMY	RT-5	RT-2	-3	7131

As you can see above, the rating difference is essentially calculated using New Rating - Old Rating.

I want to create a Scatter Plot which shows the following:

• X-Axis using Old Rating as Categories
• Y-Axis showing Rating Difference
  • HOWEVER, the tricky part is - how do I show the same Customer ID on the Y-axis twice? I don't want the Rating Difference to be summarised, but every time I change it to a Text, the scatter plot doesn't work.
  • Ideally, as an example from the dataset I put above:
    
    • There should be two dots on Old Rating RT-3 for customer BOB. One which is a little higher due to having a Rating Difference of 3, and one which is slightly lower on RT-3 due Rating Difference of 2. THERE SHOULD BE NO SUMMARIZING / AGGREGATION on rating differences. 
• For the Legend, I want to be switching between the Scenarios.
• As an added bonus, I want the size of the dots to also be dependent on the Value ($) column.

The tricky part right now I'm stuck on, is showing the same customer on the scatter plot separately without the rating differences being aggregated. Any help on what approach to take?

I've done a quick mockup here, if its any help:

Thanks!

[amitchandak]

@Anonymous , Create a concatenated column and try

A new column like

New key = [Customer ID] & " " & [Old Rating] & " " & [New Rating]

[Anonymous]

@amitchandak Why a concateneate column though? I cannot display such keys in the Scatter Graph, as the end result of Customer ID + Old Rating + New Rating won't be of much use to someone if they looked at the graph, as it'll likely be confusing to the end-user.

And where would I use the concatenate column? If I use it under detail, I get the following error: "Remove Details to display x- and y-axis pairs. Alternaitvely you can keep Details and set a summarization for x- and y-axis."

In addition, it seems I'm forced to use numbers for the Y-Axis, except they always force me to Summarize them...

[v-deddai1-msft · Accepted solution]

Hi @Anonymous,

Scatter plot requires an aggregation for x and y axis and size. Please refer to the similar post: https://community.powerbi.com/t5/Desktop/Scatter-plot-summarizing-data/td-p/37229

Best Regards,

Dedmon Dai