If logic - not producing expected results

[Anonymous]

What I want:

If given employee is the client's Lead Advisor, then add up $,

then for remaining records, if this employee is  Originator1, add up $*fraction, 

then for remaining records, if this employee is  Originator2, add up $*fraction, 

then for remaining records, if this employee is  Originator3, add up $*fraction,

otherwise give me zero. 

An employee can fill multiple roles, so I have employees who are both LeadAdvisor and an Originator. But I don't want clients to be double counted - I want them counted only once, in the order prescribed above.

The problem I'm having is that my formula below is adding everything together. It's adding in the full $ amount if they are Lead Advisor, then adding in $* fraction if they are also Originator1 or Originator2 or Originator3.

I thought that by putting the Lead Advisor statement first that it would essentially take every record where that is true and exclude them from the remaining statements. So when Power BI gets to the second statement (is this employee Originator 1?), it wouldn't also include records that were already picked up in the first statement (is this employee Lead Advisor?)

So my end result is a giant number that is incorrect; clients are being double-counted.

The formula I used:

if('QuarterlyRelationshipTotals'[Value]=QuarterlyRelationshipTotals[Employee] &&

QuarterlyRelationshipTotals[Attribute] ="LeadAdvisor",

'QuarterlyRelationshipTotals'[AUM],

if('QuarterlyRelationshipTotals'[Value]=QuarterlyRelationshipTotals[Employee] &&

QuarterlyRelationshipTotals[Attribute]="Originator1" ,

'QuarterlyRelationshipTotals'[Originator1Pcnt]/100*QuarterlyRelationshipTotals[AUM],

if('QuarterlyRelationshipTotals'[Value]=QuarterlyRelationshipTotals[Employee] &&

QuarterlyRelationshipTotals[Attribute]="Originator2",

'QuarterlyRelationshipTotals'[Originator2Pcnt]/100*QuarterlyRelationshipTotals[AUM],

if('QuarterlyRelationshipTotals'[Value]=QuarterlyRelationshipTotals[Employee] &&

QuarterlyRelationshipTotals[Attribute]="Originator3",

'QuarterlyRelationshipTotals'[Originator3Pcnt]/100*QuarterlyRelationshipTotals[AUM],

0))))

I tried adding a custom column in Power Query, it's giving me the same result as above: 

if[Value]=[Employee] and
[Attribute] = "LeadAdvisor" then
[AUM]

else

if [Value]=[Employee] and
[Attribute] = "Originator1" then
([AUM])*[Originator1Pcnt]/100

else
if [Value]=[Employee] and
[Attribute] = "Originator2" then
([AUM])*[Originator2Pcnt]/100

else
if [Value]=[Employee] and
[Attribute] = "Originator3" then
([AUM])*[Originator3Pcnt]/100

else 0

[Anonymous · Accepted solution]

I finally figured this out - it was in fact related to the structure of my data set. That logic would work in any of my other datasets, but not this particular one. 

[Greg_Deckler]

@Anonymous Hard to tell without sample data and expected results for that data. You may have better luck using a SWITCH(TRUE(), ...) statement to clean up the nested IF statements. Otherwise, Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.