Calculating percentage ytd actual to budget

[AronEpstein]

i have a powerpivot with data as follows

Cost Center	Expense Type	Month	Amount
East	Admin	Budget	10000
East	Admin	January	500
East	Admin	February	650

<TBODY>
</TBODY>

I want to calculate the total ytd (1150) as a percentage of the budget (10,000).

 

[texasalynn]

try this

Excel 2003
	A	B	C	D	E
1	Cost Center	Expense Type	Month	Amount	Percentage
2	East	Admin	Budget	10000	90%
3	East	Admin	January	500	4%
4	East	Admin	February	650	6%
Sheet7

Cell Formulas
Range	Formula
E2	=D2/SUM($D$2:$D$4)
E3	=D3/SUM($D$2:$D$4)
E4	=D4/SUM($D$2:$D$4)

 

[powerpivotpro]

Aron how about two simple CALCULATE measures and then a ratio measure?

[Budget Measure] =
CALCULATE(SUM(TableName[Amount]), TableName[Month] = "Budget")

[Actuals Measure] =
CALCULATE(SUM(TableName[Amount]), TableName[Month] <> "Budget")

[Ratio] =
[Actuals Measure] / [Budget Measure]

There are many ways in which this approach is incomplete, but depending on your needs this might be perfect.

 

[AronEpstein]

Thanx. This was perfect.