Advanced Power BI Scatter charts

[RockandGrohl]

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 Number	Asset	Cost	Unit of Measure (Quant1)	Unit of Measure (Quant2)	Quant1	Quant2	Rate	Explanation
123	Pipe	£4,000	mm diameter	metre	500	30	£133.33	Rate is asset cost / metres
123	Liner	£2,000	mm diameter	metre	500	30	£66.66	Rate is asset cost / metres
123	Management Fee	£10,000	Single item	--	1	--	£10,000	Rate is single cost
123	Prep work	£1,000	Single item	--	1	--	£1,000	Rate is single cost
567	Pipe	£6,400	mm diameter	metre	900	14	£457.14	Rate is asset cost / metres
567	Liner	£2,900	mm diameter	metre	900	14	£207.14	Rate is asset cost / metres
567	Management Fee	£15,000	Single item	--	1		£15,000	Rate is sinlge cost
567	Prep Work	£1,420	Single item	--	1		£1,420	Rate is single cost

 

[RockandGrohl]

Bump