Forum Discussion
Issues with subtotals
| Challenge 1 - Sum the Team Sales and return those sale for each person on the team while keeping each non-team member (i.e. Employees 4+5) separate as their own non related line item. Challenge 2 - In the Team A Subtotal, return the total for the team vs. summing each employee line item. Challenge 3 - The manager should be the total of Team A plus the 2 Employees who do not belong to Team A. | ||||||||
| Result issues in red. | ||||||||
| I've tried the following solution from this post but its not working for this circumstance. I've also tried modifying this solution but have been unsuccessful in getting it to work. | ||||||||
| https://community.powerbi.com/t5/Desktop/Sum-of-values-based-on-distinct-values-in-other-column/m-p/96539 | ||||||||
| Measure = MAXX(DISTINCT(Hierarchy[Team]),MAX(Sales[Sales])) | ||||||||
| SumSales = SUMX(DISTINCT(Hierarchy[Team]),[Measure]) | ||||||||
| Sales MTD = CALCULATE([SumSales]),DATESMTD(Sales[Result Period])) | ||||||||
| Sales YTD = CALCULATE([SumSales]),DATESYTD(Sales[Result Period])) | ||||||||
| Hierarchy and Sales tables are related to one another using a HierarchyKey with a one to many relationship. | ||||||||
| Correct Result | Monthly | YTD through March | ||||||
| Manager | Employee Team | Employee Name | Sales | Target | % | Sales | Target | % |
| Manager 1 | Team A | Employee 1 | 90000 | 60000 | 150% | 270000 | 180000 | 150% |
| Manager 1 | Team A | Employee 2 | 90000 | 60000 | 150% | 270000 | 180000 | 150% |
| Manager 1 | Team A | Employee 3 | 90000 | 60000 | 150% | 270000 | 180000 | 150% |
| Team A Subtotal | 90000 | 60000 | 150% | 270000 | 180000 | 150% | ||
| Manager 1 | Employee 4 | 10000 | 30000 | 33% | 30000 | 90000 | 33% | |
| Manager 1 | Employee 5 | 10000 | 30000 | 33% | 30000 | 90000 | 33% | |
| Manager 1 | 110000 | 120000 | 92% | 330000 | 360000 | 92% | ||
| Result I'm Getting | Monthly | YTD through March | ||||||
| Manager | Employee Team | Employee Name | Sales | Target | % | Sales | Target | % |
| Manager 1 | Team A | Employee 1 | 60000 | 60000 | 100% | 180000 | 180000 | 100% |
| Manager 1 | Team A | Employee 2 | 30000 | 60000 | 50% | 90000 | 180000 | 50% |
| Manager 1 | Team A | Employee 3 | 0 | 60000 | 0% | 0 | 180000 | 0% |
| 90000 | 180000 | 50% | 270000 | 540000 | 50% | |||
| Manager 1 | Employee 4 | 10000 | 30000 | 33% | 30000 | 30000 | 100% | |
| Manager 1 | Employee 5 | 10000 | 30000 | 33% | 30000 | 30000 | 100% | |
| Manager 1 | 110000 | 240000 | 46% | 330000 | 600000 | 55% |
HI Anonymous,
I'd like to suggest you take a look at the following links about using the if statement to check the current row context level and handle multiple aggregations of measure total level calculations:
Clever Hierarchy Handling in DAX - SQLBI
Measure Totals, The Final Word
Regards,
Xiaoxin Sheng
2 Replies
- lbendlin
Super User
Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with. Avoid posting screenshots of your source data if possible.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 HI Anonymous,
I'd like to suggest you take a look at the following links about using the if statement to check the current row context level and handle multiple aggregations of measure total level calculations:
Clever Hierarchy Handling in DAX - SQLBI
Measure Totals, The Final Word
Regards,
Xiaoxin Sheng
