# Power Pivot Question_ Counting Pivot

#### portalturks

##### New Member
Hi All,
I'm a rookie about PowerPivot and I'm trying to develop a tool by using DAX formulas but stuck at some point.
Attached you will find the data structure that I'm working on: there are 3 "Tables" named Opp, Users, KPI and there are relationships between tables. (e.g. 'Opp[Opp_Owner] column is related with 'Users[Full_Name] )
What I'd like to do with PowerPivot is to calculate targets by following a rational which I'll describe later and write it to 'Users [Target1] and 'Users[Target2], in attacted excel I filled it with yellow. What formula needs to do is to;

• Look at 'Users[Division]
• if it is "AE" bring 'Users[Full_Name] and count 'Opp[Opp_Owner] return a number e.g. for Leo Marc 3
• multiply it with the corresponding number at 'KPI[Target1]. e.g. for AE with 5
• if the [Division] is not "AE" but something at 'KPI[Division] it will multiply the outcome with a constant 10 (e.g. 30*10 for SR)
• else 0
What I was able to write so far is nearly nothing it seems like below:
Which functions together I need to use in order to make it possible?
Your help is very much appreciated.

Opp Table
 Country Opp_Name Opp_Owner x Price Leo Marc x Volume Leo Marc x Volume Leo Marc y Volume Portal Placis z Volume Shape Co z Volume Shape Co z Volume Carrmoto Di

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

Users Table
 Country Full_Name Division Target1 Target2 x Leo Marc AE y Portal Placis SR z Shape Co SR M

<colgroup><col><col><col span="3"></colgroup><tbody>
</tbody>

KPI Table
 Division Target1 Target2 AE 5 15 SR 30 15 HC 30 10

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.