portalturks
New Member
- Joined
- Jul 18, 2019
- Messages
- 1
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;
=IF('User DivMap'[DivisionAdj]="AE",??????????,LOOKUPVALUE(Table1[Visits],Table1[Division],'User Div Map'[Division Adj])*10)
Which functions together I need to use in order to make it possible?
Your help is very much appreciated.
Opp Table
<colgroup><col><col><col></colgroup><tbody>
</tbody>
Users Table
<colgroup><col><col><col span="3"></colgroup><tbody>
</tbody>
KPI Table
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>
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
=IF('User DivMap'[DivisionAdj]="AE",??????????,LOOKUPVALUE(Table1[Visits],Table1[Division],'User Div Map'[Division Adj])*10)
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>