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
[TABLE="class: head"]
<tbody>[TR]
[TH][/TH]
[TH][/TH]
[TH][/TH]
[TH][/TH]
[TH][/TH]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Sum of Planned Income[/TD]
[TD]Sum of Actual Income[/TD]
[TD]TargetMet[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jill[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Safeway[/TD]
[TD]

50000​

[/TD]
[TD]

60000​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Walmart[/TD]
[TD]

30000​

[/TD]
[TD]

35000​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jill Total[/TD]
[TD]

80000​

[/TD]
[TD]

95000​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Marty[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Trader Joe's[/TD]
[TD]

60000​

[/TD]
[TD]

50000​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Woolworth[/TD]
[TD]

20000​

[/TD]
[TD]

20000​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Marty Total[/TD]
[TD]

80000​

[/TD]
[TD]

70000​

[/TD]
[TD]

0​

[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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?

[TABLE="width: 668"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Row Labels[/TD]
[TD]Sum of Planned Income[/TD]
[TD]Sum of Actual Income[/TD]
[TD]TargetMet[/TD]
[TD]Sum of TargetMet2[/TD]
[TD]TargetMet FIXED[/TD]
[/TR]
[TR]
[TD]Jill[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Safeway[/TD]
[TD]50000[/TD]
[TD]60000[/TD]
[TD]60000[/TD]
[TD]60000[/TD]
[TD]60000[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]30000[/TD]
[TD]35000[/TD]
[TD]35000[/TD]
[TD]35000[/TD]
[TD]35000[/TD]
[/TR]
[TR]
[TD]Jill Total[/TD]
[TD]80000[/TD]
[TD]95000[/TD]
[TD]95000[/TD]
[TD]95000[/TD]
[TD]95000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Marty[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Trader Joe's[/TD]
[TD]60000[/TD]
[TD]50000[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Woolworth[/TD]
[TD]20000[/TD]
[TD]20000[/TD]
[TD]20000[/TD]
[TD]20000[/TD]
[TD]20000[/TD]
[/TR]
[TR]
[TD]Marty Total[/TD]
[TD]80000[/TD]
[TD]70000[/TD]
[TD]0[/TD]
[TD]20000[/TD]
[TD]20000[/TD]
[/TR]
</tbody>[/TABLE]



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.