Forum

Count from table if...
 
Notifications
Clear all

Count from table if division is "AE"

2 Posts
2 Users
0 Reactions
84 Views
(@portalturks)
Posts: 1
New Member
Topic starter
 

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.

 
Posted : 19/07/2019 9:59 am
(@mynda)
Posts: 4761
Member Admin
 

Hi Ozgur,

Welcome to our forum. Can you please upload your file again as it's not attached. You need to click the yellow 'Start Upload' button after navigating to the file you want to attached.

Thanks,

Mynda

 
Posted : 19/07/2019 10:54 pm
Share: