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

    Calculating Net gain based on multiple values in a dimension

    Megha Bhatnagar

      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
          swaroop.gantela

          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

           

           

          Please see workbook v10.5 attached in the Forum Thread:

          Calculating Net gain based on multiple values in a dimension