1 Reply Latest reply on Jan 23, 2017 9:37 PM by Shinichiro Murakami

    Populate row values with a single value on hierarchy expansion

    Justin Pulley

      I have a hierarchy that consists of Project_Number, PO_Number, and Location

       

      I have measure values of Percent Expended, Capex, and PO_Amount

       

      AGG(Percent expended) is the agg of  calculated value IF ISNULL(ATTR([PO_Number])="*") THEN SUM([SUM(s.PO_Amount)])/SUM([Capex]) ELSE SUM([SUM(s.PO_Amount)])/SUM([Capex]) END

       

      SUM(PO_Amount) is SUM(SUM(S.PO_Amount))

       

      Capex is SUM(Capex)

       

      now Capex is Associated with Project_Number and not tied directly to PO_Number; what I need to do when I expand the hierarchy is get Capex to populate each line and perform the percent expended calculate based on the whole Capex amount.

       

      Example:

       

      Not Expanded

       

          

      Project_NumberPercent ExpendedCapexPO Amount
      AH5616.85%135,00022,750
      JU096.99%100,0006,987.25

       

       

      Expanded

                 

           

      Project_NumberPO_NumberPercent ExpendedCapexPO Amount
      AH56135,000
      123456705,000
      123456705,000
      123456703,000
      123456709,750
      JU090
      US2664535.19%100,0005,187.25
      US26663101,800

       

      The first thing I can't for the life of me figure out is why the second Project is showing the NULL PO_Number with 0 and then assigning the Capex to the first PO, but the First record isn't and the percent expended is disappearing.  The second thing I need to do is make all the expanded Capex rows the same so I can derive individual percent expended.  Any Ideas? no I cannot share an actual workbook as the data is all confidential.

        • 1. Re: Populate row values with a single value on hierarchy expansion
          Shinichiro Murakami

          Hi Justin,

           

          I don't know your original data format, then I assume below.

           

           

          [Capex]

          if [Category] ="Capex" then [Value] end

           

          [PO Amount Value]

          if [Category]="PO Amount" then [Value] end

           

           

          Because PO# has duplicated row and unique ID is only amount, I needed add "[PO Amount Dimension]" to explode by amount.

          [PO Amount Dimension]

          [PO Amount Value]

           

          [Percent 1]

          {fixed [PO Number]:sum(if [Category]="PO Amount" then [Value] end)}/

          {fixed [PO Number]:sum(if [Category]="Capex" then [Value] end)}

          [Percent 2]

          {fixed [Project Number]:sum(if [Category]="PO Amount" then [Value] end)}/

          {fixed [Project Number]:sum(if [Category]="Capex" then [Value] end)}

           

           

          [Percent 3]

          if (sum([PO Amount Dimension]))>1 and sum([Capex]) >0 then attr([Percent 2])

          elseif (sum([PO Amount Dimension]))>1 then attr([Percent 1])

          end

           

          Crate hierarchy with [Project Name], [PO Number] and [PO Amount Dimension]

           

           

           

           

           

           

           

          Thanks,

          Shin

          2 of 2 people found this helpful