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
CountryOpp_NameOpp_Owner
xPriceLeo Marc
xVolumeLeo Marc
xVolumeLeo Marc
yVolumePortal Placis
zVolumeShape Co
zVolumeShape Co
zVolumeCarrmoto Di

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Users Table
CountryFull_NameDivisionTarget1Target2
xLeo MarcAE
yPortal PlacisSR
zShape CoSR M

<colgroup><col><col><col span="3"></colgroup><tbody>
</tbody>


KPI Table
DivisionTarget1Target2
AE515
SR3015
HC3010

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Watch MrExcel Video

Forum statistics

Threads
1,102,620
Messages
5,487,902
Members
407,614
Latest member
fuslela

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top