Announcements
cancel
Showing results for
Did you mean:
Contributor II

## Calculating same average value for a KPI as in the table

Hi everyone,

I am trying to make aligned the very last column of the table, which is RangeAvg of the MS1-MS6 percentages.

I am using the following formula for the KPI (trying to find an average for the RangeAverage and aggregate for all orders):

Avg(Aggr(RangeAvg([MS1 Quality],[MS2 Quality],[MS3 Quality],[MS4 Quality],[MS5 Quality],[MS6 Quality]),OrderNo))

Thank you!

Polina

Labels (1)
• ### General Question

1 Solution

Accepted Solutions
Contributor II
Author

Just found the solution - you have to aggregate by all dimensions you have in the table. Except for OrderNo I also had a couple of columns with dates. So, basically, it should look like this:

Avg(Aggr(RangeAvg([Measure 1],[Measure 2],...[Measure n]),Dimension 1, Dimension 2,...Dimension n))

6 Replies
Creator II

If it is always MS1-MS6, could you just do the following for the KPI?

(avg([MS1 Quality]) + ... + avg([MS1 Quality])) / 6

If not, could you provide some more details about the data structure? What is OrderNo used for? An small sample table of data would be useful as well if you can provide one.

Contributor II
Author

Hi,

No, this is not working - (avg([MS1 Quality]) + ... + avg([MS1 Quality])) / 6.

I tried to test a formula for average for MS1 first, but it gave me 96% instead of expected 98%. I used "Total" after Set Expression.

Please find attached the sample data.

Polina

Creator II

I may be misunderstanding the data structure. I assumed it looked like the table on the left in my screenshot below. With that structure, I was able to get the numbers to match using

avg([MS1 Quality] + [MS2 Quality] + [MS3 Quality] + [MS4 Quality] + [MS5 Quality] + [MS6 Quality]) / 6

Champion III

Works fine for me

AVG(aggr(RangeAvg([MS1 Quality],[MS2 Quality],[MS3 Quality],[MS4 Quality],[MS5 Quality],[MS6 Quality]),OrderNo))

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Champion III

can you post the app itself?

check if the KPI is tagged to any alternate state

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Contributor II
Author

Just found the solution - you have to aggregate by all dimensions you have in the table. Except for OrderNo I also had a couple of columns with dates. So, basically, it should look like this:

Avg(Aggr(RangeAvg([Measure 1],[Measure 2],...[Measure n]),Dimension 1, Dimension 2,...Dimension n))