10 Replies Latest reply on May 4, 2018 8:01 AM by David Luhut

# Compare MOST (IF NOT ALL) fields of the same fact table and return the result

Hello

Assume I have the following fact table

Profit CenterDateGLFAAmount
10010100Jan 2018DZ110
10010100Jan 2018AZ15
10010100Jan 2018BZ120
10010100Jan 2018CZ115
10010100Jan 2018DZ15
10010100Jan 2018AZ125
10010100Jan 2018BZ1

20

And After a JOIN with another table, I "added" 2 of the following columns [Category] and [SubCategory]

Profit CenterDateGLFACategorySub CategoryAmount
10010100Jan 2018AZ1RevPAS
10010100Jan 2018BZ1RevPAS
10010100Jan 2018CZ1RevPAS
10010100Jan 2018DZ1RevenueGross Rent10
10010100Jan 2018AZ1RevenueVacancy5
10010100Jan 2018BZ1RevenueOther20
10010100Jan 2018CZ1RevenueGross Rent15
10010100Jan 2018DZ1RevenueVacancy5
10010100Jan 2018AZ1RevenueOther25
10010100Jan 2018BZ1RevenueGross Rent20

How can I do a Calculated Field that return the value from the fact Table itself, whenever the [Category] is 'RevPAS', and that all of the fields (before the JOIN), in this case except 'Category' and 'SubCategory', is the same.

For example, looking at the 1st row, it is 'RevPAS', thus lookup the table and search for [Category] = 'Revenue'  AND  ([Sub Category] = 'Gross Rent'  OR  [Sub Category] = 'Vacancy') AND IF ALL FIELDS of the fact Table which is [Profit Center] to [FA] are the same, then return the [Amount].

Desired Result

Profit CenterDateGLFACategorySub CategoryAmountCalculated Field Result
10010100Jan 2018AZ1RevPAS5
10010100Jan 2018BZ1RevPAS20
10010100Jan 2018CZ1RevPAS15
10010100Jan 2018DZ1RevenueGross Rent10Null
10010100Jan 2018AZ1RevenueVacancy5Null
10010100Jan 2018BZ1RevenueOther20Null
10010100Jan 2018CZ1RevenueGross Rent15Null
10010100Jan 2018DZ1RevenueVacancy5Null
10010100Jan 2018AZ1RevenueOther25Null
10010100Jan 2018BZ1RevenueGross Rent20Null
• ###### 1. Re: Compare MOST (IF NOT ALL) fields of the same fact table and return the result

Here you go:

Hope this helps. A 10.5 workbook is attached.

1 of 1 people found this helpful
• ###### 2. Re: Compare MOST (IF NOT ALL) fields of the same fact table and return the result

Thanks Hari!!!

Do I have to write down all of the fields?

• ###### 3. Re: Compare MOST (IF NOT ALL) fields of the same fact table and return the result

I guess you meant which fields to be included in the FIXED statement. You need to write only those fields that you want them to have the same value.

• ###### 4. Re: Compare MOST (IF NOT ALL) fields of the same fact table and return the result

Are you able to 'downgrade' it to v10.4?

• ###### 5. Re: Compare MOST (IF NOT ALL) fields of the same fact table and return the result

Here you go.

• ###### 6. Re: Compare MOST (IF NOT ALL) fields of the same fact table and return the result

Thanks for the attached workbook.

I tried to BLEND the data with a simple table

Profit Center     Units

10010100          15

And then create a calculated field named MTD where it should show me the desired value (below)

MTD

RevPAS            2.67

Revenue           100

Where RevPAS is 40 / 15 = 2.67  and

Revenue is still just the SUM([Amount]) when [Category]='Revenue'

But no matter how I try to do it, it doesn't allow me to.

One of the formula that I created is

MTD:=

IF [Category] = 'RevPAS' THEN

SUM([Derived Value]) / SUM([Sheet1 (UnitTest)].[Units])

ELSE

SUM([Amount])

END

• ###### 7. Re: Compare MOST (IF NOT ALL) fields of the same fact table and return the result

You may be getting an error in the formula, stating you cannot mix aggregate and non-aggregate values.

Please rewrite the formula as given below. Category is enclosed within ATTR().

MTD:=

IF ATTR([Category]) = 'RevPAS' THEN

SUM([Derived Value]) / SUM([Sheet1 (UnitTest)].[Units])

ELSE

SUM([Amount])

END

1 of 1 people found this helpful
• ###### 8. Re: Compare MOST (IF NOT ALL) fields of the same fact table and return the result

Thank you!

I learnt something new today!

If there's a reps up, let me know so that I can increase your reputation.

Once again, thank you!

• ###### 9. Re: Compare MOST (IF NOT ALL) fields of the same fact table and return the result

You are very welcome.

• ###### 10. Re: Compare MOST (IF NOT ALL) fields of the same fact table and return the result

Hi Hari,

I tried to implement the same idea...it works on the 'mini trial'

but when I tried to incorporate to the real data, it doesn't work.

I've posted on a forum on a different thread Get the result of LOD of SubHeader  and  assign it to the Header

I'd deeply appreciate if you could help me out with this problem.