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:
=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.
Beginner Question

 Posts: 1
 Joined: Thu Jul 18, 2019 10:59 pm
Beginner Question
 Attachments

 Power Pivot Example.xlsx
 (11.24 KiB) Downloaded 9 times
Re: Beginner Question
Hi portalturks,
Please see attached, look at Users table under the data model for Target 1 and Target 2 calculated columns.
Formula use is:
I suspect there would be more efficient ways to tackle the problem but am guessing the full requirements are not just populating Target 1 and 2 in the Users table.
Best practice is to use measures rather than calculated columns (google this to see reasons), so potentially your approach could be modified to use measures instead.
Cheers
Phil
Please see attached, look at Users table under the data model for Target 1 and Target 2 calculated columns.
Formula use is:
{L_CODE}{L_COLON} {L_SELECT_ALL_CODE}
=if(Users[Division]="AE",COUNTROWS(RELATEDTABLE(Opp))*LOOKUPVALUE('KPI'[Target1],'KPI'[Division],Users[Division]),10*LOOKUPVALUE('KPI'[Target1],'KPI'[Division],Users[Division]))
I suspect there would be more efficient ways to tackle the problem but am guessing the full requirements are not just populating Target 1 and 2 in the Users table.
Best practice is to use measures rather than calculated columns (google this to see reasons), so potentially your approach could be modified to use measures instead.
Cheers
Phil
 Attachments

 Power Pivot Example PPF.xlsx
 (241.21 KiB) Downloaded 6 times
Who is online
Users browsing this forum: No registered users and 3 guests