1 Reply Latest reply on Nov 7, 2018 9:26 PM by swaroop.gantela

# Calculating Net gain based on multiple values in a dimension

I have two fields Incumbents (previous winner) and winner (new winner). I want to calculate the net gain amount, which would be total won-total loss.

To calculate total loss for each incumbent (AMZ, ESI, Magellan, Navitus, Other) I have to calculate the following:-

for AMZ if incumbent is AMZ and winner <> AMZ then loss

Similarly for ESI if incumbent is ESI and Winner <> ESI then loss

FOr WIn:-

for AMZ If Incumbent  <> AMZ but winner = AMZ then Win

I want to do these calculations is one field so that I could have to measures Loss and Win and the calculate the Net gain for each Incumbent and Winner

End goal is to have a cross tab report of all the Winners and the net gain amount.

Attaching a mock workbook for reference

• ###### 1. Re: Calculating Net gain based on multiple values in a dimension

Megha,

I don't think I quite got there, but maybe this can give ideas.

There are most probably other easier, more straightforward ways to do this.

The need is to merge the Incumbent and the Winner into one column so as to

merge the losses and gains.

To do so, I unioned the dataset to itself, which generated a new [Table Name] field

which can be used in calculations.

Here is the merged winner-incumbent column:

IF [Table Name]="Sheet1" THEN [Winners (group)]

ELSEIF [Table Name]="Sheet11" THEN [Incumbents (group)]

END

As you had described Amount Gained was:

IF [Incumbents (group)]<>[Winners (group)] THEN [Impact Rev] END

and the negative version for Amount Lost.

I assumed the granularity of the data was the combination of Account Name and Business Unit

so I calculated the Loss/Gain at that level:

{ FIXED [Merged Winner - Loser Name], [Table Name],[Account Name],[Business Unit]:

SUM(IF [Table Name]="Sheet1" THEN [Amount Gained] END)}/2

// note needed the divide by 2 to get it right

So then the merged amount became:

IF [Table Name]="Sheet1" THEN [Fix Gain to Winner]

ELSEIF [Table Name]="Sheet11" THEN [Fix Loss to Incumbent]

END