Forum Discussion

SebaSpotti's avatar
SebaSpotti
Icon for Advocate II rankAdvocate 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...
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! 

SamWiseOwl's avatar
SamWiseOwl
Icon for Super User rankSuper 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
Icon for Advocate II rankAdvocate 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.