# 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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

1,106,550
Messages
5,512,004
Members
408,872
Latest member
Lorid24