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

# Populate row values with a single value on hierarchy expansion

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_Number Percent Expended Capex PO Amount AH56 16.85% 135,000 22,750 JU09 6.99% 100,000 6,987.25

Expanded

 Project_Number PO_Number Percent Expended Capex PO Amount AH56 135,000 1234567 0 5,000 1234567 0 5,000 1234567 0 3,000 1234567 0 9,750 JU09 0 US266453 5.19% 100,000 5,187.25 US266631 0 1,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

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