Beginner Question

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

Beginner Question

Postby portalturks » Thu Jul 18, 2019 11:45 pm

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.
Attachments
Power Pivot Example.xlsx
(11.24 KiB) Downloaded 14 times

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

Re: Beginner Question

Postby PhilC » Mon Jul 22, 2019 1:16 pm

Attachments
Power Pivot Example PPF.xlsx
(241.21 KiB) Downloaded 11 times


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 1 guest