Using Set Analysis with If Condition based on Dimensional Value

[marc_wintjen]

Hi,  I'm trying to build an Expression against a 2 dimensional Chart (Year and Category) and I want to use set analysis when the Category = "Budget vs Actual" but a normal expression like "=Sum([Gross Sales])"when the Category is anything else.  The Categories are "Budget, Actual, Variance" and when I try to calculate the Set Analysis of "

=

sum({<Category = {'Budget'} >} [Total Discount]) / sum({<Category = {'Budget'} >} Gross Sales])

[CELAMBARASAN]

Hi,

     Try with this expression

     =if(Category='Budget vs Actual',Sum([Gross Sales]), sum( [Total Discount]) / sum(TOTAL <Year,Category> [Gross Sales]))

Hope it helps

Celambarasan

[lironbaram · Accepted solution]

hi check the attach file

the discount test expression

you cant use set analysis because it only selects a category as catgeory is in your dimension it cant work

the trick is to use total in your expresssion

[marc_wintjen]

That worked perfectly - Thanks!  I didn't realize you can do an if statement inside of an aggregation!