Advanced Power BI Scatter charts

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
801
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I have a dataset I am looking to create a scatter chart based on two different sets of data, which analyses cost for various components of a project.


The first type of data I have would look at the cost per metre ("rate") of materials on the Y axis, with the X axis containing the diameter ("quant1")of the material.


The second type of data I have would look at "Asset" cost against the project costs as a whole, so it needs to sumif based on Project number. To give you all a better understanding of my requirements, please see the below example data.


This is all held in a database which is imported to Power BI, and it would be good to have a single filterable scatter chart to view these. Theres a couple things I would like to happen here:

  1. I need to filter with a dropdown list on Asset type to select one single item at a time
  2. If that item's primary UoM is "mm diameter", then I need Quant1 on the X-axis and Rate on the Y-axis. This will show the rate per square meter vertically and the asset size horizontally
  3. If that item's primary UoM is "Single item" then I need Rate on the X-axis and the sum of the entire project on the Y axis.
    1. So using project 123 as an example, the total project cost is £17,000 on the Y axis, with "Management Fee" being £10,000 on the horizontal axis.

Is this possible to change which data fields go into each axis based on the lookup of a dropdown selected? In terms of process, you would select "Pipe", it would lookup the Unit of Measure which is "mm diameter", then it ensures that Quant1 is on the X-axis and Rate is on the Y-axis.

Conversely, if you selected "Management Fee" in the Asset filter drop down, it would lookup the UoM and return "Single item" then put the rate on the X-axis with the SUMIFS(Cost, Project Number, 123) in the Y-axis.


Not sure on the scope of PowerBI so if this is impossible don't hold back on calling me a dumb-dumb.

Thanks!

Project NumberAssetCostUnit of Measure (Quant1)Unit of Measure (Quant2)Quant1Quant2RateExplanation
123Pipe£4,000mm diametermetre50030£133.33Rate is asset cost / metres
123Liner£2,000mm diametermetre50030£66.66Rate is asset cost / metres
123Management Fee£10,000Single item--1--£10,000Rate is single cost
123Prep work£1,000Single item--1--£1,000Rate is single cost
567Pipe£6,400mm diametermetre90014£457.14Rate is asset cost / metres
567Liner£2,900mm diametermetre90014£207.14Rate is asset cost / metres
567Management Fee£15,000Single item--1£15,000Rate is sinlge cost
567Prep Work£1,420Single item--1£1,420Rate is single cost
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top