Forum Discussion

Pan_Forex's avatar
Pan_Forex
Icon for Helper III rankHelper III
3 years ago
Solved

Average from every day

Hi guys! I wonder if it is possible to create a table that adds new rows every day and leaves the old ones unchanged. I have a table that is updated several times every day with new rows and a second table that counts the average for each player. 

 

     Date   ID Player Score
20.01.2023  1025  A       8       
20.01.20231025B4
20.01.20231025C5
20.01.20231025D7
18.01.20231020A6
18.01.20231020B5
18.01.20231020C5
18.01.20231020D5
18.01.20231020E4
18.01.20231020F6
17.01.2023958F5
17.01.2023958C6
17.01.2023958D5
17.01.2023958E3

Now it looks like this:

Player              Average Score
A7
B4.5
C5.33
D5.66
E3.5
F5.5

I would like the new table to count such a ranking from each day. It would add new updated scores every day, while leaving the old scores unchanged:

DatePlayer     Average Score
20.01.2023      A7
20.01.2023B4.5
20.01.2023C5.33
20.01.2023D5.66
18.01.2023A6
18.01.2023B5
18.01.2023C5.5
18.01.2023D5
18.01.2023E3.5
18.01.2023F5.5
17.01.2023F5
17.01.2023C6
17.01.2023D5
17.01.2023E3

 

  • tamerj1's avatar
    tamerj1
    3 years ago

    Pan_Forex 

    Please try

    New Table =
    ADDCOLUMNS (
    SUMMARIZE ( 'Table', [Date], 'Table'[Player] ),
    "Average Score",
    CALCULATE (
    AVERAGE ( 'Table'[Score] ),
    ALLEXCEPT ( 'Table', 'Table'[Player] ),
    [Date] <= EARLIER ( [Date] )
    )
    )

  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi Pan_Forex 

    this shall also work:

    Table = 
ADDCOLUMNS(
    SUMMARIZE(
        TableName,
        TableName[Date],
        TableName[Player]
    ),    
    "Avg",
    VAR _date=TableName[Date]
    RETURN CALCULATE(
    AVERAGE(TableName[Score]),
    TableName[Date]<=_date
    )
)

     

     

6 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi Pan_Forex 

    please try

    Average Score =
    CALCULATE (
    AVERAGE ( 'Table'[Score] ),
    ALLEXCEPT ( 'Table', 'Table'[Player] ),
    [Date] <= MAX ( [Date] )
    )

    • Pan_Forex's avatar
      Pan_Forex
      Icon for Helper III rankHelper III
      3 years ago

      thank you for your reply. Your solution works but I would still like to have this data in a new table and not as a measure. Is this possible? 

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        3 years ago

        Pan_Forex 

        Please try

        New Table =
        ADDCOLUMNS (
        SUMMARIZE ( 'Table', [Date], 'Table'[Player] ),
        "Average Score",
        CALCULATE (
        AVERAGE ( 'Table'[Score] ),
        ALLEXCEPT ( 'Table', 'Table'[Player] ),
        [Date] <= EARLIER ( [Date] )
        )
        )

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi Pan_Forex 

    this shall also work:

    Table = 
ADDCOLUMNS(
    SUMMARIZE(
        TableName,
        TableName[Date],
        TableName[Player]
    ),    
    "Avg",
    VAR _date=TableName[Date]
    RETURN CALCULATE(
    AVERAGE(TableName[Score]),
    TableName[Date]<=_date
    )
)

     

     

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    Pan_Forex , try a measure with help from the date table

     

    Cumm Sales = CALCULATE(AverageX(Values('Date'[Date]), CALCULATE(SUM(Table[Score]))) ,filter(all('Date'),'Date'[date] <=max('Date'[date])))

    • Pan_Forex's avatar
      Pan_Forex
      Icon for Helper III rankHelper III
      3 years ago

      Thank you for your reply. I would like the average to be calculated for each player separately and not all together from each day, and to create a new table with results only.