Average calculation discrepancy: KPI shows 49.70% while Line Chart reference line shows correct 48.25%

[frapaco]

Hi community,

I’m experiencing an inconsistency between two visuals using the same calculated field. Here’s my setup:

1. I created a calculated field ‘AVG_per_ID’:
   avgOver(VALUE, [{ID}], PRE_AGG)

2. I have two visuals:

a) KPI Visual:

• Value: AVG_per_ID (aggregated as average)
• This shows 49.70%

b) Line Chart:

• X-axis: ID
• Y-axis: VALUE
• Added a reference line using AVG_per_ID (set as average)
• The reference line shows 48.25%

The reference line shows the correct calculation I need (48.25%), but I can’t get the same result in the KPI visual despite using the same calculated field.

[chart 1: KPI showing 49.70%]
[char

t 2: Line Chart with reference line showing 48.25%]

Here’s my complete sample dataset:
ID | DATE | VALUE
1 | 202504 | 0.95
1 | 202504 | 0.01
2 | 202504 | 1.00
2 | 202504 | 0.00
3 | 202504 | 1.00
3 | 202504 | 0.00
4 | 202504 | 0.19
4 | 202504 | 0.00
5 | 202504 | 0.888
5 | 202504 | 0.68
5 | 202504 | 0.617
5 | 202504 | 0.511
5 | 202504 | 0.08
6 | 202504 | 0.53
6 | 202504 | 1.00

Expected calculation:

1. First average by ID:

• ID 1: (0.95 + 0.01) / 2 = 0.48
• ID 2: (1.00 + 0.00) / 2 = 0.50
• ID 3: (1.00 + 0.00) / 2 = 0.50
• ID 4: (0.19 + 0.00) / 2 = 0.095
• ID 5: (0.888 + 0.68 + 0.617 + 0.511 + 0.08) / 5 = 0.5552
• ID 6: (0.53 + 1.00) / 2 = 0.765

2. Then average of these averages:
   (0.48 + 0.50 + 0.50 + 0.095 + 0.5552 + 0.765) / 6 = 0.4825 (48.25%)

How can I get the KPI visual to show the same correct average (48.25%) that appears in the line chart reference line?

Thanks in advance for your help!

[DylanM]

Hello @frapaco, welcome to the QuickSight Community!

Are the same filters applied to the line chart and the KPI? Since you are using PRE_AGG as the calculation level, a difference in filtering between the 2 visuals could cause a discrepancy in the value that is being returned.

[prantika_sinha]

Hi @frapaco ,

For your KPI calculation, since the data is referenced at lowest granularity available, the average values is calculated based on each record available :

However for your line chart, the backend data looks something like :

For this, we will need either to use the calculation in a Table visual and modify the cosmetics to make it look like KPI , or create the calculation based on post aggregation avg.

I have added both solution option here, since I am not aware of the use case. Feel free to explore whichever suits the use case.

46799- sample workaround

Thanks,
Prantika

[frapaco]

Yes Dylan, there are the same filters.
Thank you!

[frapaco]

Amazing! Thank you so much, it works!

Just a couple of questions to understand better.

1. Why do you insert a calculated filed with : " " only?
2. Why do you use a pivot table to show a KPI? is it a different way to obtain the right result or do you recommend me to always use pivot table than kpi chart?
3. I think I set the same properties you set, why it looks different? (screenshot)
   
   

Thank you so much!!

[frapaco]

In addition, I like the alternative way to obtain the right result but I’d like to use the KPI chart because I need to compare the data with a fix target and I believe it is impossible with the Pivot chart.

Thank you!

[prantika_sinha]

If we use post age calculation level, it will look for the reference which we cannot add in the KPI chart.

For Pivot, the dummy field " " is added because we want to collapse the calculation and show the total with average aggregate. Keeping ID only will not allow us to collapse the records to show single record.