Calculating same average value for a KPI as in the table

[mamaewapolya]

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))

Please advise what might be wrong in my calculation or logic.

Thank you!

Polina

[KGalloway]

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.

[mamaewapolya]

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

[KGalloway]

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

[vinieme12]

Works fine for me

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

[vinieme12]

can you post the app itself?

check if the KPI is tagged to any alternate state

[mamaewapolya · Accepted solution]

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))