Forum Discussion

SebaSpotti's avatar
SebaSpotti
Advocate II
1 year ago
Solved

Weight Rolling Averages

Hi all!

I have 6 rolling averages calculate each in this way:

Rolling AVG 1 = 
VAR CurrentDate = SELECTEDVALUE(Month[DateMonth])
VAR PreviousDate = DATE(year(CurrentDate),month(CurrentDate)-12,day(CurrentDate))
VAR Result =
CALCULATE(
    AVERAGE(Table1[Evaluation]),
    FILTER(
        'Table1',
        Table1[DateMonth] >= PreviousDate && Table1[DateMonth] <= CurrentDate)

)RETURN
Result

I want to to calculate a weight average of those 6 averages were also the weigths are rolling weights, calculated in this way:

Rolling Votes Table1 = 
VAR CurrentDate = SELECTEDVALUE(Month[DateMonth])
VAR PreviousDate = DATE(year(CurrentDate),month(CurrentDate)-12,day(CurrentDate))
VAR Result =
CALCULATE(
    DISTINCTCOUNT(Table1 [ID]),
    FILTER(
        'Table1 ',
        Table1 [DateMonth] >= PreviousDate && Table1[DateMonth] <= CurrentDate)

)RETURN
Result

do you have any idea how can I solve it?

 

Thanks!!

Sebastiano

Need Help
  • SebaSpotti's avatar
    SebaSpotti
    1 year ago

    I think I find an easy solution: 

    WeightedAvgOfRollingAverages = 
DIVIDE(
    SUMX(
        {
            [Rolling AVG Table1] * DISTINCTCOUNT(Table1[ID]),
            [Rolling AVG Table2] * DISTINCTCOUNT(Table2[ID]),
            [Rolling AVG Table3] * DISTINCTCOUNT(Table3[ID]),
            [Rolling AVG Table4] * DISTINCTCOUNT(Table4[ID]),
            [Rolling AVG Table5] * DISTINCTCOUNT(Table5[ID]),
            [Rolling AVG Table6] * DISTINCTCOUNT(Table6[ID])
        },
        [Value]
    ),
    [TotalWeights]
)

    where TotalWeights is simply a new measure:

    TotalWeights = DISTINCTCOUNT(Table1[ID])+DISTINCTCOUNT(Table2[ID])+...+DISTINCTCOUNT(Table6[ID])

    It seems working! 

3 Replies

  • SamWiseOwl's avatar
    SamWiseOwl
    Super User
    1 year ago

    Hi SebaSpotti 

    I believe what you are looking for is AggregateX or specifically AverageX in this case.

    AverageX(
                    Table to calculate over

                     ,expression for each row
    )

     

    So maybe something like:
    AverageX( table1, Divide([Rolling AVG 1], [Rolling Votes Table1]),Blank())

     

    Hopefully someone with better maths will come along ....

    • SebaSpotti's avatar
      SebaSpotti
      Advocate II
      1 year ago

      I think I find an easy solution: 

      WeightedAvgOfRollingAverages = 
DIVIDE(
    SUMX(
        {
            [Rolling AVG Table1] * DISTINCTCOUNT(Table1[ID]),
            [Rolling AVG Table2] * DISTINCTCOUNT(Table2[ID]),
            [Rolling AVG Table3] * DISTINCTCOUNT(Table3[ID]),
            [Rolling AVG Table4] * DISTINCTCOUNT(Table4[ID]),
            [Rolling AVG Table5] * DISTINCTCOUNT(Table5[ID]),
            [Rolling AVG Table6] * DISTINCTCOUNT(Table6[ID])
        },
        [Value]
    ),
    [TotalWeights]
)

      where TotalWeights is simply a new measure:

      TotalWeights = DISTINCTCOUNT(Table1[ID])+DISTINCTCOUNT(Table2[ID])+...+DISTINCTCOUNT(Table6[ID])

      It seems working! 

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi SebaSpotti 

         

        I am happy to learn that you have solved the problem, please accept your reply as a solution, it will make it easier for other users with similar problems to find this post and benefit from it.

        Thank you in advance for your cooperation!

         

        Best Regards,
        Jarvis Tang
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.