Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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]) )
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]) )
@Phil_Seamark thank you for your help! Sorry it took me so long to respond. This worked wonderfully.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |