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
[TABLE="width: 223"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Country[/TD]
[TD]Opp_Name[/TD]
[TD]Opp_Owner[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]Price[/TD]
[TD]Leo Marc[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]Volume[/TD]
[TD]Leo Marc[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]Volume[/TD]
[TD]Leo Marc[/TD]
[/TR]
[TR]
[TD]y[/TD]
[TD]Volume[/TD]
[TD]Portal Placis[/TD]
[/TR]
[TR]
[TD]z[/TD]
[TD]Volume[/TD]
[TD]Shape Co[/TD]
[/TR]
[TR]
[TD]z[/TD]
[TD]Volume[/TD]
[TD]Shape Co[/TD]
[/TR]
[TR]
[TD]z[/TD]
[TD]Volume[/TD]
[TD]Carrmoto Di[/TD]
[/TR]
</tbody>[/TABLE]
Users Table
[TABLE="width: 338"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD]Country[/TD]
[TD]Full_Name[/TD]
[TD]Division[/TD]
[TD]Target1[/TD]
[TD]Target2[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]Leo Marc[/TD]
[TD]AE[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]y[/TD]
[TD]Portal Placis[/TD]
[TD]SR[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]z[/TD]
[TD]Shape Co[/TD]
[TD]SR M[/TD]
[TD]
[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
KPI Table
[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Division[/TD]
[TD="class: xl65, width: 64"]Target1[/TD]
[TD="class: xl65, width: 64"]Target2[/TD]
[/TR]
[TR]
[TD]AE[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]SR[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]HC[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]10[/TD]
[/TR]
</tbody>[/TABLE]
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
[TABLE="width: 223"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Country[/TD]
[TD]Opp_Name[/TD]
[TD]Opp_Owner[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]Price[/TD]
[TD]Leo Marc[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]Volume[/TD]
[TD]Leo Marc[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]Volume[/TD]
[TD]Leo Marc[/TD]
[/TR]
[TR]
[TD]y[/TD]
[TD]Volume[/TD]
[TD]Portal Placis[/TD]
[/TR]
[TR]
[TD]z[/TD]
[TD]Volume[/TD]
[TD]Shape Co[/TD]
[/TR]
[TR]
[TD]z[/TD]
[TD]Volume[/TD]
[TD]Shape Co[/TD]
[/TR]
[TR]
[TD]z[/TD]
[TD]Volume[/TD]
[TD]Carrmoto Di[/TD]
[/TR]
</tbody>[/TABLE]
Users Table
[TABLE="width: 338"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD]Country[/TD]
[TD]Full_Name[/TD]
[TD]Division[/TD]
[TD]Target1[/TD]
[TD]Target2[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]Leo Marc[/TD]
[TD]AE[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]y[/TD]
[TD]Portal Placis[/TD]
[TD]SR[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]z[/TD]
[TD]Shape Co[/TD]
[TD]SR M[/TD]
[TD]
[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
KPI Table
[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Division[/TD]
[TD="class: xl65, width: 64"]Target1[/TD]
[TD="class: xl65, width: 64"]Target2[/TD]
[/TR]
[TR]
[TD]AE[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]SR[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]HC[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]10[/TD]
[/TR]
</tbody>[/TABLE]