7 Replies Latest reply on Oct 30, 2017 11:22 AM by Shinichiro Murakami

# Build a table with different calculation per row

Hi,

I am trying to build a table that shows YTD, This month etc........ for several KPI's

Some of the rows I will be using sum, then average and also dividing one KPI by another.

I have managed to sum based on parameter date and one KPI just not clear how to create the other calculations.

I have attached a sample data set.

Kev

• ###### 1. Re: Build a table with different calculation per row

Hi Kevin

I am not sure I understnd your question correctly or not, anywasy

Adding else 0 will eliminate 'nulls' and you can remove KPI from the table.

Thanks,

shin

• ###### 2. Re: Build a table with different calculation per row

Hi Shin,

Thanks for responding am probably just not being clear I need to keep the KPI's.

I want to be able to carry out different calculations based on the KPI column.

For example

I want to be able to check for KPI then do a calculations based on this.

for example in the same calculated field.

IF [KPI] = 'KPI 1 Average' Then Average if dates match etc........

Elseif

IF [KPI] = "KPI 2 Sum' Then Sum ifs dates match etc....

• ###### 3. Re: Build a table with different calculation per row

Hi Kevin,

Just as two example of  2 KPIs

Thanks,

Shin

1 of 1 people found this helpful
• ###### 4. Re: Build a table with different calculation per row

Thanks Shin,

This makes more sense to me now.

Do you know why the average function showing a different result to when I use the sum function divided by the number of months?

• ###### 5. Re: Build a table with different calculation per row

Hi Kevin,

Ned further investigation but ,I guess that is based on granularity level.

Avg is working on line item basis, and if you want to calc avg of (sum of month), formula should be sum of month / number of month.

If your expected result is sum / count of months, you'd better use it.

Thanks,

Shin

• ###### 6. Re: Build a table with different calculation per row

Hi Shin,

I used datepart with my parameter date work fine.

It looks like the avg function dividing by the full count which is 21 no matter how many values/months

I use the datepart function just now but be interested to know if you can use the avg function.

Thanks again.

Kev

• ###### 7. Re: Build a table with different calculation per row

You can use "avg" function I believe, but that requires much much more complicated formula in each calculated field.

The reason is LOD calculation is captureing all the period of last year  months + this year 9 month =21.

Limiting those period to only 9 month with LOD formula is quite complicated.  (Not impossible, but not worth spending time for me)

Thanks,

Shin