Calculating the difference from a Specific Running Average

[Dallas Dye 2]

I am working on finding the average of a specific value in a filter but have that value in a column on every row, below is an example and attached is an example file.

The goal is to calculate the difference in order quantities from theAverage Product Type Avg. I can not figure out the Avg. Product Type Quantites formula.

 

i've used the formula below but it only adds the average to teh qualifigying Avg. rows, the small and large rows are blank.

if([Product Type] in ("Avg"),Avg([Quantity Ordered]),Null)

 

 

 

Customer

Product Type

Order quantity

Avg. Product Type Quantities

 

 

A

small

5

6.5

 

 

B

avg

6

6.5

 

 

C

avg

7

6.5

 

 

D

small

1

6.5

 

 

E

Large

4

6.5

[Kirsten Smith (she/her)]

I've attached a DXP file that shows one way to solve this issue. I created new columns for the sum for the selected Product type and another for the number of rows, then used a Calculated Value Property Control to display the result. There's also a drop-down control to select which Product type to average.

[Dallas Dye 2]

Thank you for the File Kristen but i dont' think i was clear, i'm trying to create the Avg. Product Type Quantities as a calcualted column, i hand entered the values to show what i was looking for.  

 

 

 

i've been researching how to do a drop down in text area, and i appreciate your additons to the file! 

[Kirsten Smith (she/her)]

You can use this expression to create the new column:

 

case when [Product Type]=DocumentProperty("AvgProd") then Sum([Values to average]) / First([Divisor]) else NULL end