3 Replies Latest reply on Sep 29, 2016 12:31 AM by ra.sae.0

# Runtime Aggregate Calculation with filters

Hi All,

Need expert advice on how to achieve this view.

Here I am trying to find out numbers of customers, respective Sales and Profit numbers, which is segregated by Region.

This view also has Profit Breakdown for each Region with Customer level granularity.

So ideally I expect #customers with all breakdown buckets per region.

E.G.

But When user selects any of the filters, it should re-calculate Breakdown based on filter selection

In this scenario, one customer might have different breakdown status when measured at Product Name filter.

Here is the breakdown bucket at higher level.

Profit Breakdown per customer:

If SUM([Sales]) > 0 AND SUM([Profit]) > 0

Then 'Upper'

ELSEIF SUM([Sales]) > 0 AND SUM([Profit]) < 0

Then 'Lower'

ELSEIF SUM([Sales]) > 0 AND SUM([Profit]) = 0

Then 'Equal'

ELSEIF SUM([Sales]) <= 0

Then 'NetNew'

END

Appreciate help on this topic.

I have attached my workbook for example.

Cheers!!!

• ###### 1. Re: Runtime Aggregate Calculation with filters

Hi Shrikant,

I gave a try on this. I have used Window Calculations to calculate total Sales, no of Customer , Profit & Customer Name Dimension in the Detail to achieve this.

Please verify it once whether I am right and rename the measures.

I have Unchecked Ignore Table Calculations (Right Click on the view) of Profit Breakdown Dimension.

Edited the view to look good . (Moved the Text Column in the last to be hidden) Please see to the attached file

Hope this helps.

Final output:

Sales_Window:

window_sum(sum([Sales]))

# Customer_Window:

window_count(min([Customer Name]))

Profit_Window:

window_sum(sum([Profit]))

• ###### 2. Re: Runtime Aggregate Calculation with filters

Thanks for the solution, it worked perfectly!!!!

• ###### 3. Re: Runtime Aggregate Calculation with filters

Great to hear that