@lizrowden wrote:
I need a suggestion on how to accomplish a drill down. I am in SQL server direct query mode.
Simplified form of the data looks as follows:
| Technician |
Invoice # |
Parts Revenue |
Warranty Labor |
Contract Labor |
Billable Labor |
| Ed |
12345 |
$ 33.23 |
$ 100.00 |
$ 100.00 |
$ 100.00 |
| John |
12345 |
$ 34.33 |
$ 100.00 |
$ 100.00 |
$ 100.00 |
| Bill |
12346 |
$ 49.99 |
$ 100.00 |
$ 100.00 |
$ 100.00 |
I have created a new measure in the report to sum the labor columns (Total Labor = SUMX('Reporting TechnicianProductivity','Reporting TechnicianProductivity'[GratisLabor]+'Reporting TechnicianProductivity'[NonbillableLabor]+'Reporting TechnicianProductivity'[RevenueLabor]+'Reporting TechnicianProductivity'[RevenueWarrantyLabor]+'Reporting TechnicianProductivity'[GratisLaborOt]+'Reporting TechnicianProductivity'[NonbillableLaborOt]+'Reporting TechnicianProductivity'[RevenueLaborOt]+'Reporting TechnicianProductivity'[RevenueContractLaborTravel]) and have that 'Total Labor' number, as well as, a number of the other revenue categories, in a stacked column chart visualization by Technician, which is perfect for the end-user. I do not want the individual labor categories to be broken down in the main visualizations. I also have a Table visualization with the same details that are in the bar graph (but have this information by technician and invoice number). However, the end-users would like to be to drill down and see break-down of that information that makes up 'Total Labor' if necessary. Could anyone suggest some options?
It seems that you may look for ALLEXCEPT? How does this measure go in your case?
Total Labor =
SUMX (
ALLEXCEPT (
'Reporting TechnicianProductivity',
'Reporting TechnicianProductivity'[Technician]
),
'Reporting TechnicianProductivity'[GratisLabor]
+ 'Reporting TechnicianProductivity'[NonbillableLabor]
+ 'Reporting TechnicianProductivity'[RevenueLabor]
+ 'Reporting TechnicianProductivity'[RevenueWarrantyLabor]
+ 'Reporting TechnicianProductivity'[GratisLaborOt]
+ 'Reporting TechnicianProductivity'[NonbillableLaborOt]
+ 'Reporting TechnicianProductivity'[RevenueLaborOt]
+ 'Reporting TechnicianProductivity'[RevenueContractLaborTravel]
)
