Calculating percentage ytd actual to budget

[AronEpstein]

i have a powerpivot with data as follows
[TABLE="width: 100, align: left"]
<TBODY>[TR]
[TD]Cost Center[/TD]
[TD]Expense Type[/TD]
[TD]Month[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]East[/TD]
[TD]Admin[/TD]
[TD]Budget[/TD]
[TD]10000[/TD]
[/TR]
[TR]
[TD]East[/TD]
[TD]Admin[/TD]
[TD]January[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]East[/TD]
[TD]Admin[/TD]
[TD]February[/TD]
[TD]650[/TD]
[/TR]
</TBODY>[/TABLE]








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.