powerpivotlegal
New Member
- Joined
- May 14, 2014
- Messages
- 30
Hello everybody.
I don't know if I am making my problem more difficult than it is or if I'm asking powerpivot to do too much.
I work in the finance department of a law firm so my data is a little different than Sales/Products. I have multiple matter numbers and fees billed on various days. I have separate lookup tables so that I could create relationships between the worked date, fees billed, office. This allows me to drop in slicers for month/year and country.
The problem comes in trying to tie these slicers with calculating the budget remaining for each matter. Each matter has a budget (located on a separate lookup table). However, if I create Calculated Columns to obtain the remaining budget for each matter, the slicers don't have a relationship with these amounts.
I thought of creating a measure such as:
RemainBudget m1002:=sumx(FILTER(MatterLookup,MatterLookup[Matter]="68284000-001002"),MatterLookup[Project Budget])-sumx(filter(DataDumpFees,DataDumpFees[Matter]="68284000-001002"),DataDumpFees[Value of Time Billed])
but I would need a separate measure for each matter number to look up the matter number and budget, sum up the fees billed for that matter and calculate the remaining budget. Although the slicers would work, the resulting pivot table would then have separate 'remaining budget' columns, which would be too messy.
I can't seem to find a solution on any of the posts.
Any help would be greatly appreciated.
I don't know if I am making my problem more difficult than it is or if I'm asking powerpivot to do too much.
I work in the finance department of a law firm so my data is a little different than Sales/Products. I have multiple matter numbers and fees billed on various days. I have separate lookup tables so that I could create relationships between the worked date, fees billed, office. This allows me to drop in slicers for month/year and country.
The problem comes in trying to tie these slicers with calculating the budget remaining for each matter. Each matter has a budget (located on a separate lookup table). However, if I create Calculated Columns to obtain the remaining budget for each matter, the slicers don't have a relationship with these amounts.
I thought of creating a measure such as:
RemainBudget m1002:=sumx(FILTER(MatterLookup,MatterLookup[Matter]="68284000-001002"),MatterLookup[Project Budget])-sumx(filter(DataDumpFees,DataDumpFees[Matter]="68284000-001002"),DataDumpFees[Value of Time Billed])
but I would need a separate measure for each matter number to look up the matter number and budget, sum up the fees billed for that matter and calculate the remaining budget. Although the slicers would work, the resulting pivot table would then have separate 'remaining budget' columns, which would be too messy.
I can't seem to find a solution on any of the posts.
Any help would be greatly appreciated.