Power Pivot Question_ Counting Pivot

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;

  • 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.


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]
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Forum statistics

Threads
1,223,886
Messages
6,175,191
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top