Add average numbers as benchmark onto scatter plot

[Anonymous]

Hi,

I need help with adding a second series of data to my scatter plot. I am currently plotting Sales vs. Margin % by Customer and would like to add the averages of all customers as another series to benchmark how my customer is doing. 

My initial thought is to create a new table via the Summarize function that joins my original data to my averages with a column specifying if an average or additional customer data. The only problem is that my averages are dynamic measures that can change via the industry (3 industries) and the hierarchy of the customer (Level 1 and 2), a third measure may also be added to calculate averages by Top, Medium and Bottom tier customers (by Sales). 

Unfortunately, I cannot provide a sample data set, but I have included some screenshots of examples.

[dax]

Hi @Anonymous , 

I am not clear about your requirement, if possible could you please explain this to me in details and inform me more detailed information(such as your expected output and your sample data, you don't need to provide real data)? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Zoe @dax ,

Thanks for your reply! I included screenshots of the expected output and a fake dataset, can you be more specific on what you need to be clarified?

Basically it is a scatter chart that has sales as an x-axis and margin % as a y-axis. I would like to plot both the data (by product) for a specific company, as well as the average (by product) of all companies, in two different colours identified by a legend (Customer A, Industry Average). 

I am predicting that there will be another problem when it comes to the average because its currently being automatically calculated by Power BI when I make a table with the columns: Product, % Margin, $ Net Sales, as these are measures.  When you add the dimension of customers, you get the customer level data that is currently plotted, by I want an added dimension that compares it to the average.

[dax · Accepted solution]

Hi  @Anonymous , 

Based on my test, I find that the scatter chart seems not support multiple metrics in chart(can't use two measures in same axis of chart). It seems that it could use calculated columns in same axis. So I think you might need to create summary table which store sale and margin % as columns.

By the way, you also could submit this in power-bi-ideas . 

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Not sure if images were correctly added to the post: