Subtotal for Calculated Column in Power Pivot Table

[ndbennett]

I want to sum the results of a calculated column in a PowerPivot calculation but the calculation is actually being performed on the subtotals also - is there a way I can change this?

The calculation is TargetMet=IF([Sum of Actual Income]>=[Sum of Planned Income],1,0)

I would like the subtotals in the TargetMet column for both Jill and Marty to reflect the number of stores that have met their targets, but the calculation is being performed on the subtotals instead.

Any help would be appreciated!

Excel 2013 32 bit

		Sum of Planned Income	Sum of Actual Income	TargetMet
	Jill
	Safeway	50000​	60000​	1​
	Walmart	30000​	35000​	1​
	Jill Total	80000​	95000​	1​
	Marty
	Trader Joe's	60000​	50000​	0​
	Woolworth	20000​	20000​	1​
	Marty Total	80000​	70000​	0​

<tbody>
</tbody>

<tbody>
</tbody>

 

[mart37]

why not the calclulated column in power pivot?

=if(Table1[Planned Income]<=Table1[actual Income];1;0)

 

[SOQLee]

Unless you need the 1,0 flags, I would forgo these in the formula. Here's what I got. The table is named 'Range'. TargetMet2 is a calculated column, while TargetMet and TargetMet FIXED are measures. Writing measures seems overly complicated; I would just use a calculated column. Thoughts anyone?

Row Labels	Sum of Planned Income	Sum of Actual Income	TargetMet	Sum of TargetMet2	TargetMet FIXED
Jill
Safeway	50000	60000	60000	60000	60000
Walmart	30000	35000	35000	35000	35000
Jill Total	80000	95000	95000	95000	95000
Marty
Trader Joe's	60000	50000	0	0	0
Woolworth	20000	20000	20000	20000	20000
Marty Total	80000	70000	0	20000	20000

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

TargetMet2=if(Range1[Planned Income]<=Range[Actual Income],[Actual Income],0)

TargetMet:=IF([Sum of Actual Income]>=[Sum of Planned Income],[Sum of Actual Income],0)

TargetMet FIXED:=IF([Sum of Actual Income]>=[Sum of Planned Income],[Sum of Actual Income],SUMX(DISTINCT('Range'[Actual Income]),[TargetMet]))

 

[ndbennett]

Thanks for your reply. The purpose of the 1,0 flag was that I want to apply commission to sales only where the target is met or exceeded, so actual less than target does not qualify for commission.

I thought I had found a workaround - all the data is extracted from MS Access so I added an IIF calculation in the Access table to calculate the difference only if target was met or exceeded. It works well in Access but unfortunately is appears that Excel cannot extract user defined fields from Access, so no data transfer.

With these restrictions, I am thinking that I need to work off each sales person's sub total and forget the flag.