Get the Average of the Sum

[Veq21984]

I have this data set columns

Code

Location

Date

Hour

Interval

Prices

Quantity

Code = Date & Hour & Interval

Date = mm/d/yyyy

Hour = 01-24

Interval = 05,10,15,20,25,30,35,40,45,50,55,60 per Hour

Prices are values

Quantity are values

Filters: Year, Month, Date

I would like to display, in KPI , the average of per interval sum of quantities depending on my filter of choice

Steps:

Step1. sum of Quantity of Locations A, B & C per Interval

Step2. Average of Step1

See attached file for sample data set

[rubenmarin]

Hi, if the Avg is by interval you can use an avg of the sum aggreting by interval, like:

Avg(Aggr(Sum(Quantity),Interval))

[Veq21984]

Thanks Ruben for the response,

I tried that but it does not give me the right value that I expect.

Step1. sum of Quantity of Locations A, B & C per Interval

Step2. Average of Step1

Step3. Value will change if Location is specifically chosen.

For example:

If Location = A, B & C; Month = January, Year = 2023

the expected Quantity value = 11,207

Using the Avg(Aggr(Sum(Quantity),Interval)) will result to 113.3M

Anyone...

[rubenmarin]

Hi, if I use the data attached, the selections returns a Sum of 213M, and an Avg of 17M, which icoincides with 213 divided by the 12 intervals.

[TauseefKhan]

Hi @Veq21984,

Check this one: 

Avg(Aggr(Sum({<Location=P(Location)>} Quantity), Interval))