topic Calculating same average value for a KPI as in the table in App Development

Calculating same average value for a KPI as in the table

mamaewapolya — Mon, 23 Jan 2023 15:28:15 GMT

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

Re: Calculating same average value for a KPI as in the table

KGalloway — Mon, 23 Jan 2023 22:16:35 GMT

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.

Re: Calculating same average value for a KPI as in the table

mamaewapolya — Tue, 24 Jan 2023 05:31:50 GMT

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

Re: Calculating same average value for a KPI as in the table

KGalloway — Tue, 24 Jan 2023 14:26:23 GMT

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

Re: Calculating same average value for a KPI as in the table

vinieme12 — Wed, 25 Jan 2023 06:28:30 GMT

Works fine for me

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

Re: Calculating same average value for a KPI as in the table

vinieme12 — Wed, 25 Jan 2023 06:30:43 GMT

can you post the app itself?

check if the KPI is tagged to any alternate state

Re: Calculating same average value for a KPI as in the table

mamaewapolya — Wed, 25 Jan 2023 08:54:56 GMT

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