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.




      Not Expanded



      Project_NumberPercent ExpendedCapexPO Amount






      Project_NumberPO_NumberPercent ExpendedCapexPO Amount


      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.




          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])



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










          2 of 2 people found this helpful