NEED HELP! Need Measure or calculated columns to analyze remaining budgets linked to slicers

[powerpivotlegal]

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.

 

[Tianbas]

Not sure if I 100% understand the problem and data model but if there is a relationship between both tables based on Matter a simple calculated measure in the pivot table should do it
=Sum(MatterLookup[Project Budget]-Sum(DataDumpFees[Fees billed])

 

[powerpivotlegal]

Thanks, but that formula gives me an error as a measure or a calculated column.

I've set out the data and bridge/lookup tables below. There are relationships on the matter number and worked date from the DataDumpFees with the two lookup tables to link all three. However, after I create a calculated column of remaining budget in the matter lookup table and want to slice by "Month" from the Date Look up table, I get the message, "relationship may be needed".

DataDumpFees

Matter	Matter Name	TK Office	Work Date	Value of Time Billed
68284000-001002	IP Rights Analysis - Tax	Palo Alto	20 Feb 2014	252.00
68284000-001002	IP Rights Analysis - Tax	San Francisco	29 Nov 2013	556.00
68284000-001002	IP Rights Analysis - Tax	San Francisco	20 Feb 2014	764.50
68284000-001002	IP Rights Analysis - Tax	Palo Alto	20 Feb 2014	525.00
68284000-001002	IP Rights Analysis - Tax	San Francisco	20 Feb 2014	514.50
68284000-001003	Infinity Intangible Property - Tax	San Francisco	18 Nov 2013	417.00
68284000-001003	Infinity Intangible Property - Tax	San Francisco	19 Nov 2013	764.50

<tbody>
</tbody><colgroup><col><col><col><col><col></colgroup>

Date Lookup

Worked Date	Calendar Year	Month
10/1/2013	2013	10-October
10/2/2013	2013	10-October
10/3/2013	2013	10-October

<tbody>
</tbody><colgroup><col><col><col></colgroup>

Matter Lookup

Matter	Matter Name	Project Budget
68284000-001002	IP Rights Analysis - Tax	$ 500,000.00
68284000-001003	Infinity Intangible Property - Tax	$ 100,000.00

<tbody>
</tbody><colgroup><col><col><col></colgroup>

 

[scottsen]

I think I'm with tianbas. (His code was just missing a paren, if that's the onlly error msg?)

PowerPivot is going to complain when you slice on dates because you have no relationship between the dates and budget table... just ignore the error.

 

[Tianbas]

based on the model above you should not even get the "relationship may be needed" warning

In the pivottable use matter in rows (from Matter Lookup), value of time billed and project budget in value field. Add month slicer. Create the new calculated measure "delta budget" in the pivot table below matter lookup.

=sum('Matter Lookup'[Project Budget])-sum(DataDumpFees[Value of Time Billed])

result looks as following

Row Labels	Sum of Value of Time Billed	Sum of Project Budget	Delta Budget
68284000-001002	261200	500000	238800
68284000-001003	118150	100000	-18150
Grand Total	379350	600000	220650

<tbody>
</tbody><colgroup><col><col><col><col></colgroup>

 

[powerpivotlegal]

Thanks Tianbas. The measure works great as a stand alone. Adding in the month slicer works, but is probably the wrong choice to report the data I want. I want a cumulative or running total by month/quarter versus the matter's budget, so if I clicked on February, I would get the remaining budget after summing the fees billed in preceding months.

I think I need a TOTALYTD or TOTALQTD to do this. This is the measure I came up with, but I'm not sure.
QTD Billed Fees:=TOTALQTD(SUM(DataDumpFees[Value of Time Billed]),Month[Worked Date],all(Month))


Another weird thing is that my Country slicer will display all countries in the look up table after dropping the Remaining Budget option into the pivot table even though not all countries have fees. Without the Remaining Budget option, the slicer will shade out the countries without fees.