Home Forums Power Pivot LOOKUPVALUE issue

This topic contains 3 replies, has 2 voices, and was last updated by  portalturks 1 month ago.

Viewing 4 posts - 1 through 4 (of 4 total)
• Author
Posts
• #18052

portalturks
Participant
• Started: 1
• Replies: 2
• Total: 3

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:

Which functions together I need to use in order to make it possible?

Your help is very much appreciated.

###### Attachments:
You must be logged in to view attached files.
#18054

tomallan
Keymaster
• Started: 7
• Replies: 2566
• Total: 2573

Hi PortalTurks,

The biggest issue you are facing is that you are thinking like an Excel user, not a Power Pivot user.

In a model with the 3 related tables you describe, the formula for a calculated column (not a measure) is:

=
IF (
Users[Division] = “AE”,
COUNTROWS ( RELATEDTABLE ( Opp ) ) * RELATED ( ‘KPI'[Target1] ),
IF (
NOT ( ISBLANK ( RELATED ( ‘KPI'[Division] ) ) ),
RELATED ( ‘KPI'[Target1] ) * [Constant],
0
)
)

I strongly recommend reading a basic Power Pivot book, such as “Power Pivot and Power BI” by Rob Collie or “Supercharge Excel” by Matt Allington. Both authors offer practical courses in Power Pivot, and I also recommend you choose one of them. These actions will save you from many hours of grief.

Please see attached workbook (yours, modified).

###### Attachments:
You must be logged in to view attached files.
#18060

portalturks
Participant
• Started: 1
• Replies: 2
• Total: 3

Hi Tomallan,

Thank you very much. This was actually my first Power Pivot study which I had to use Power Pivot in order to reach my study goal because otherwise the workbook is getting very non-user friendly. I’ll start with the books that you recommend to me.

Very much appreciated and see you in forums ðŸ™‚

#18093

portalturks
Participant
• Started: 1
• Replies: 2
• Total: 3

Hi

This might not directly related with Power Pivot but an issue has been occured when I’d like to “show items with no data”.

My target fields are populated by a using the formula as discussed above but the names are repeating when I activate “show items with no data” and looks like attached. Normally it should look like:

NameÂ  Â Div Adj.Â  Â Visit_TargetÂ  Â  Call_TargetÂ  Â  Opp_CountÂ  Â Â

AÂ  Â  Â  Â  Â  Â  Â  AEÂ  Â  Â  Â  Â  Â  Â  15Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  15Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  5

BÂ  Â  Â  Â  Â  Â  Â  SRÂ  Â  Â  Â  Â  Â  Â  50Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  75Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â 10

CÂ  Â  Â  Â  Â  Â  Â  AEÂ  Â  Â  Â  Â  Â  Â  0Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  0Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â 0Â  Â  Â  (when show items with no data activated)

Is this about the relationships between tables and the directions of them maybe?