SumOver in KPI chart

Hi Experts!

I need to make pretty simple aggregation, but i can’t do that.
Here details: ds_NumberOfSuppliers

Calculation works well in table with Supplier column, but doesn’t work without this column. Is there some way to get same calculation, but maybe without Over functions?

Appreciate any help or Ideas.

1 Like

Hello @iskorokhid, so the issue with your third visual is that since you added Supplier as a partition for your sumOver calculation, it will be required in the visual. Without it, QuickSight doesn’t have any way to reference the field that you want the aggregation to be based on.

What are you wanting to show in the KPI? You could do something where you average the sums for each supplier for the KPI, that might allow you to use it. It would look something like this:
avgOver({Positive Delta}, [], PRE_AGG)

You will either need to add PRE_AGG in the sumOver statement you built for Positive Delta, or you can try without it. Let me know if this helps!

Hey @DylanM! AVG with PRE_AGG doesn’t work:

I removed it and got this:
image

Anyway i can’t add this measure to KPI:
image

I want to show in KPI number of Suppliers, that have Q2 revenue - Q1 revenue < 0, for example.

Hi @iskorokhid ,

I have tried to modify the arena analysis which you shared. Here is the link Solution to sumover KPI

Have modified the revenue delta field calculation and changed the KPI visual to show supplier count and used revenue delta as filter.

Thanks,
Prantika

2 Likes

Wow, big thanks! I was thinking about using measure as filter, but again, with sumOver it doesn’t work. Interesting why sum(, ) and sumOver(, ) are not the same.

I will use your solution, thanks again!

1 Like