Need help writing a DAX measure with Groupby or Summarise to calculate row subtotals

[bluetail]

I have this table that I am using in another visual in the same report. This other visual shows my data by Project ID.

I want to groupby by Category for all Project IDs. Specifically, I need to get the sum of   " Project Value (ex VAT)" which is £1, 380,000 and "Monies remaining", £573,550,000.

I need this so I can create a gauge chart like this with "Money Remaining" as Value and "Project Value" as Maximum Value.

that said, I have found a workaround by duplicating my table and then grouping the Category column in the second table in Query Editor - this way, I can filter the Monies category from one table and filter the Project Value category fro m the other table. however, it is cumbersome. 

How can I write the two measures and add them  to my table?  I have looked at the example for GroupBy in the documentation, 

https://docs.microsoft.com/en-us/dax/groupby-function-dax  however, it seems complicated.

My attempt at this, 

Measure_total_cat = GROUPBY('Cost Summary', 'Cost Summary'[Category], "Total by category", SUMX(CURRENTGROUP(), 'Cost Summary'[Value]))

or, 

Measure_total_cat = SUMMARIZECOLUMNS('Cost Summary'[Category], "Total by category", SUM('Cost Summary'[Value]))

gives me an error,

"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

my table and pbix files are here,

https://1drv.ms/x/s!AtS3DN4JGKISiCt3JzyYDqBeZ7_m?e=rrmauF  

https://1drv.ms/u/s!AtS3DN4JGKISiCX5t-51djzyLAGj?e=kiO8ME

[DataInsights · Accepted solution]

@bluetail,

I would start by modeling your data into a star schema (fact and dimension tables). You can use this DAX calculated table for the Projects dimension table:

Projects = 
DISTINCT (
    UNION (
        DISTINCT ( 'Commercial Report'[Project ID] ),
        DISTINCT ( 'Cost Summary'[Project ID] )
    )
)

Create relationships:

Create measures:

Monies Remaining = 
CALCULATE (
    SUM ( 'Cost Summary'[Value] ),
    'Cost Summary'[Category] = "£ Monies remaining"
)

Project Value (ex VAT) = 
CALCULATE (
    SUM ( 'Cost Summary'[Value] ),
    'Cost Summary'[Category] = "Project Value (ex VAT)"
)

Add measures to the visual: