## Beginner Question

Anything related to PowerPivot and DAX Formuale
portalturks
Posts: 1
Joined: Thu Jul 18, 2019 10:59 pm

### Beginner Question

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.
Attachments
Power Pivot Example.xlsx

PhilC
Posts: 274
Joined: Tue Sep 09, 2014 8:13 am

### 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:

{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