Replicate chart in Power BI to Excel

[RockandGrohl]

Hello,

In Power BI I have a scatter chart with Project Parameters across the X, Project Cost along the Y, with each scatter chart corresponding to a Project ID. There are effectively two series of data here, for the different type of contract each project can be under, let's call them contract A and contract B.

Here's an example of how the data is laid out:

Project ID	Parameter	Cost	Contract
V123	400	200000	A
V2245	20	40000	B
V2665	59	213100	A
V4545	600	265000	A
V6654	123	54300	B

What I can do in Power BI is set the X to Parameter, the Y to Cost, the Values as Project ID and then finally, by setting the Legend to "Contract" it separates each set of points into its own series with this example having 3 A dots and 2 B dots.



This is the way my central database is laid out. My question is, how do I do this in Excel? With Excel scatter charts, I create a series, but as I can only set a series name & give it X and Y values, there's no way to tell the chart to separate the different types of contract for each Project.

Another thing I'm running in to is that I use slicers linked to the database page so the user can filter the chart to show different types of Projects.

My brain has melted today, can anyone let me know how to do this? The database is refreshed regularly so I can't sit there and select individual values. Thanks

 

[RockandGrohl]

The only way I can get something that looks similar to Power BI is to separate the Contract A & Contract B into their own sub-tables, and set the series to reflect the column for each. But that then runs the issue in that I cannot use one slicer to filter both tables to the same project type.


EDIT: Nevermind, I am mistaken. I can have two tables controlled by one slicer... this changes things then.

 

[RockandGrohl]

Actually, scratch that. While it appears initially that one slicer can filter both tables, it actually can't.

 

[RockandGrohl]

What I've had to do is take the table generated from Power Query, make a Pivot table from it and use the Contract as the column headers. This creates a separate, dynamically updating column for each contract type which has just the relevant data points in.