Color code Products on a scatter plot Based on Profit and Quantity Sold

[Anonymous]

Hi All,

I have a scatter plot of Products. The scatter plot has the axis Profitability Vs Popularity (No of Items Sold). Initial dataset is in the following format.

Date, Product, Profit ,ItemsSold

2017-01-01,AA,500,30

2017-01-02,AA,400,20

2017-01-01,BB,600,20

2017-01-06,BB,700,40

2017-02-19,CC,1000,90

2017-02-20,CC,1500,95

2017-02-21,CC,2000,105

 and so on.  

I plot the products on the scatter plot. where X-axis=Sum[Profit] Y-axis=Sum[ItemsSold]

Then i am creating following measures to group the products.

TotalProfit = SUM(PRICE_LEVEL[Profit])

AllProfit = CALCULATE(SUM(PRICE_LEVEL[Profit]),ALLSELECTED(PRICE_LEVEL[Product]))

Profitability Margin = CALCULATE(DIVIDE([TotalProfit],[TotalPortionsSold]),ALLSELECTED(PRICE_LEVEL))

TotalPortionsSold = SUM(PRICE_LEVEL[ItemsSold])

AllPortionsSold = CALCULATE(SUM(PRICE_LEVEL[ItemsSold]),ALLSELECTED(PRICE_LEVEL[Product]))

NumberofMaterial = CALCULATE(DISTINCTCOUNT(PRICE_LEVEL[MATERIAL]),ALLSELECTED(PRICE_LEVEL))

Popularity Margin = CALCULATE(DIVIDE([TotalPortionsSold],[NumberofMaterial])*0.7,ALLSELECTED(PRICE_LEVEL))

Then I categorize Products as high and Low Profitable and Popular as follows.

Profitabilty = if([TotalProfit]< [Profitability Margin],"L","H")

Popularity = if([TotalPortionsSold]<[Popularity Margin],"L","H")

Then Based on H and L combinations I give names to those categories. Basically i need to divide products in to four categories. I have done it using following measure.

Quadrant = CALCULATE(if([Profitabilty]="H" && [Popularity]="H","Q1",if([Profitabilty]="H" && [Popularity]="L","Q2",if([Profitabilty]="L" && [Popularity]="H","Q3","Q4"))))

Now i need to color code the scatter plot based on the above quadrant. 

My question is i cannot add [Quadrant] to the legend as it is a measure. 

Does any one of you know how can i achieve this? 

Regards,

Maddy

[GilbertQ]

There is a new visual which might be able to solve your issue

It is called Quadrant Chart

Quadrant Chart

[Anonymous]

@GilbertQ

Thanks for the reply. Actually i cannot use the quadrant chart. Beacuse for my case i cannot divide the background into four equal sections. I have the scatter plot as shown in the following chart. I need to color the Products based on the Category/Quadrant which they fall into.

Regards,

Maddy

[v-yulgu-msft]

Hi @Anonymous,

As you know, it is not available to add a measure into legend section of a scatter chart since the measure needs to be sliced to get the corresponding data. Currently, Power BI scatter chart doesn't support quadrants. Someone has submitted this request here: Ability to show quadrants on scatter chart you can click to vote it up.

To work around this requirement, apart from using the custom visual Quadrant Chart, you can consider using R script if you are fimiliar with it. Here is a similar thread for your reference: R package to create scatter plots in quadrants?

As Quadrant Scatter Chart is supported in Excel, you can create Scatter Chart in Excel and then pin the chart to Power BI Service dashboard. Pin a tile to a Power BI dashboard from Excel

Best regards,
Yuliana Gu

[Anonymous]

Hi Yuliana,

Thanks for the reply. Yes i am familiar with R and I will try the method which is given in the link.

But I have a question. Will R visual interact with the filters? 

[v-yulgu-msft]

Hi @Anonymous,

As I know, it seems that filter does not affect R visual.

Regards,
Yuliana Gu