How to get Avg function by Selected Month

[Ross1991]

                

=sum(Aggr(avg([Pieces Produced]),[2024 Jan Feb Prod.Part#]))

Above formula gives me average of Jan and Feb data.  (450+500+800)/3 = 583 (which is great).

However, can someone tell me how to modify above formula if I just want to see month of January Average?  It should be (450+500)/2= 475.  Thank you.   

[igoralcantara]

Try something like this:

Avg(
     Aggr(
          Sum({<Month = {'Jan'}>} [Pieces Produced]),
          [Part#]
     )
)

[Ross1991]

Hello  @igoralcantara ,    Instead of giving me an average of either January or February,   formula gave me sum of each part number for both months combined.  For example, it gave Part A  Quantity Produced sum of January and February combined.  Regardless of month I put in the formula, it gives sum of two month. This is what i wrote as a formula,

Avg(Aggr(Sum({<Month={'Jan'}>}[Pieces Produced]),[Part#]))

[TauseefKhan]

Hi @Ross1991,

Check this one:

Avg(Aggr(Sum({<Month = {"$(=GetFieldSelections(Month))"}>}[Pieces Produced]), [Part#]))


**********Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.********

[TauseefKhan]

To dynamically calculate the average production of each part while handling month selections, use the following expression:

Avg(
Aggr(
Sum(TOTAL <[Part#]> [Pieces Produced]) / Count(TOTAL <[Part#]> DISTINCT [Pieces Produced]),
[Part#]
)
)

Required output :
When you selected the month of January: (450+500)/2= 475.

**********Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.********

[TauseefKhan]

To calculate the average production of each part while handling month selections, use the following expression:

Avg(
Aggr(
Sum(TOTAL <[Part#]> [Pieces Produced]) / Count(TOTAL <[Part#]> DISTINCT [Pieces Produced]),
[Part#]
)
)

Required output:
When you selected the month of January : (450+500)/2= 475

**********Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.********

[Ross1991]

Avg(
Aggr(
Sum(TOTAL <[Part#]> [Pieces Produced]) / Count(TOTAL <[Part#]> DISTINCT [Pieces Produced]),
[Part#]
)
)

above formula looks promising.   I don't know where to or how to select month of "Jan" or "Feb" because my date format is 1/5/2024 for example.  I was able to use Date Picker and get the desired result by simple Avg [Pieces Produced] formula.   Can you tell me or send me the screen shot how did you select Jan?  I do see you got 475 above.   Thank you.

[TauseefKhan · Accepted solution]

You can use the Month() Function: Month(Yourdatefield) or calendar in your script.

Table_GetFieldselect:

LOAD

    Date(Date#(Date,'M/D/YYYY'),'YYYY-MM-DD') as Date,

    Resource,

    [Part#],

    [Pieces Produced],

    Month(Date(Date#(Date,'M/D/YYYY'),'YYYY-MM-DD')) as Month

INLINE [

    Date, Resource, Part#, Pieces Produced

    01/05/2024, B106, E, 450

    01/29/2024, B106, E, 500

    02/23/2024, B106, E, 800

    03/05/2024, B106, A, 050

    04/29/2024, B106, A, 200

    03/23/2024, B106, B, 100

];

[Ross1991 · Accepted solution]

You are amazing. It worked. Used Filter. Thank you. I used lot more data set for my practice. It is working. Did not know about Date in the filter like = Month()



[cid:image001.png@01DAB14E.065B40A0]

[Ross1991 · Accepted solution]

@TauseefKhan Thanks again.   I removed DISTINCT from the formula because I had lot of repeating values in my data.   Now it is working when I selected individual month or all months.  It looks like below.   

Avg(
Aggr(
Sum(TOTAL <[Part#]> [Pieces Produced]) / Count(TOTAL <[Part#]> [Pieces Produced]),
[Part#]
)
)