Separating Sub Groups from Main Groups for Sales Totals

[Bridgewater]

Hi all,

I have an excel spreadsheet with columns for our sales teams, those teams' subteams (if any) and the sales amount. I've attached an example spreadsheet for reference. My issue is this: I need to get the spreadsheet in sheet 1 to look like sheet 2 in Power BI. In other words, I need team and subteam totals displayed in one, easy-to-read table and/or chart. Example Spreadsheet

What you will notice is in Sheet 2 the Totals for Subteams D1 and D2 make up the aggregate total for Team D, however, they are still displayed and ranked in order with the rest of the teams.

I've tried different measures, calculated columns, and even a new calculated table but I keep getting stuck. 

Any help on this will be helpful.

[Phil_Seamark · Accepted solution]

HI @Bridgewater

I created a calcualted table using the following approach and I think it is close

Table 3 = 
VAR SalesSubTeam =  FILTER(Table1,'Table1'[Sales Sub Team] <> BLANK())
RETURN 
 GROUPBY(
                UNION (
				SELECTCOLUMNS(Table1,"Sales Team",[Sales Team] , "Sales Amount" , [Sale Amount]) ,
				SELECTCOLUMNS(SalesSubTeam,"Sales Team",[Sales Sub Team] , "Sales Amount" , [Sale Amount])
				),
                 [Sales Team],
                 "Sales Amount",SUMX(CURRENTGROUP(),[Sales Amount])
                 )

[Bridgewater]

@Phil_Seamark thank you for your help! Sorry it took me so long to respond. This worked wonderfully.