RockandGrohl
Well-known Member
- Joined
- Aug 1, 2018
- Messages
- 801
- Office Version
- 365
- Platform
- 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:
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!
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:
- I need to filter with a dropdown list on Asset type to select one single item at a time
- 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
- 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.
- 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 |