Sort collections by brand by percentage of profit for each brand

Question

I have a SQL server with the following columns: brand, collection, gross_profit. There are two brands, each having several collections. I am trying to sort the collections by brand as a percentage of profit they make up for each brand. A sample desired output would look like this:

brand	collection	gross_profit_percentage	gross_profit
One	OneA	34%	340
One	OneB	33%	330
One	OneC	33%	330
Two	TwoA	50%	500
Two	TwoB	40%	400
Two	TwoC	10%	100

I have tried the following, which is obviously wrong because SQL does not recognize columns that I have previously created within the same query.

SELECT 
    brand,
    collection,
    gross_profit,
    SUM(gross_profit) OVER (PARTITION BY brand, collection) AS collection_gross_profit_total,
    SUM(gross_profit) OVER (PARTITION BY brand) AS brand_gross_profit_total,
    ((collection_gross_profit_total / brand_gross_profit_total) * 100) AS gross_profit_percentage
FROM dbo.Data$    
WHERE Year='2018'
GROUP BY brand, collection, gross_profit;

Error Messages:

Msg 207, Level 16, State 1, Line 94 Invalid column name 'collection_gross_profit_total'.

Msg 207, Level 16, State 1, Line 94 Invalid column name 'brand_gross_profit_total'.

Answer 1

Another option

select t.brand,
       t.collection,
       t.gross_profit,
       t.collection_gross_profit_total, t.brand_gross_profit_total,
       ((t.collection_gross_profit_total / convert(decimal(16,2), t.brand_gross_profit_total)) * 100) AS gross_profit_percentage
from   ( SELECT brand,
                collection,
                gross_profit,
                SUM(gross_profit) OVER (PARTITION BY brand, collection) AS collection_gross_profit_total,
                SUM(gross_profit) OVER (PARTITION BY brand) AS brand_gross_profit_total
         FROM dbo.Data    
         --WHERE Year='2018'
         GROUP BY brand, collection, gross_profit
       ) t

The DBFiddle contains also another option to do it