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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,110
Messages
5,509,265
Members
408,719
Latest member
padapinto

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top