4 Replies Latest reply on Aug 29, 2018 4:07 PM by sahithi Muri

# Compare One Dimension Member to All Others With LOD Calcs

Hi Everyone,

I am currently working on a requirement to compare one dimension member value to other dimension member values.My problem is similar to this one Re: How to compare values within specific dimension?

the solution suggested is perfect and it will work for me but only problem is in the IF condition i have to use a aggregated measure(ex:sum(measure)/sum(measure)) in the IF Statement, which is giving me "cannot mix aggregate and non-aggregate measure" error.So I tried using ATTR() function for the dimension in IF condition,which is giving me error message of "LOD expressions cannot contain table calculations and ATTR() functions".I tried aggregate the dimension (company) using min/max which are not working for me either.Please suggest me a solution.

Thanks,

Sahithi.

• ###### 1. Re: Compare One Dimension Member to All Others With LOD Calcs

Any chance you could share a workbook with anonymized data or reproduce something similar using superstore?

• ###### 2. Re: Compare One Dimension Member to All Others With LOD Calcs

Hi Ken,

Thanks for your reply.

Sure, Please find the attachment.In that workbook,I created a calculation "exclude-profit ratio" which is showing "cannot mix aggregate and non-aggregate " error.now i have to aggregate sub category ,Attr() function is not supported in lod calculations .Please suggest

• ###### 3. Re: Compare One Dimension Member to All Others With LOD Calcs

Hi Sahithi,

Try this

{ EXCLUDE [Sub-Category]:SUM(IF [Sub-Category]=[select subcategory] THEN ([Profit]) END )}/

{ EXCLUDE [Sub-Category]:SUM(IF [Sub-Category]=[select subcategory] THEN ([Sales]) END )} So i have used the exclude LOD in the Profit Ratio formula. Hope this helps

• ###### 4. Re: Compare One Dimension Member to All Others With LOD Calcs

Thank you Anuvir,It worked perfectly!!