cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper III

## 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.2023 1025 B 4 20.01.2023 1025 C 5 20.01.2023 1025 D 7 18.01.2023 1020 A 6 18.01.2023 1020 B 5 18.01.2023 1020 C 5 18.01.2023 1020 D 5 18.01.2023 1020 E 4 18.01.2023 1020 F 6 17.01.2023 958 F 5 17.01.2023 958 C 6 17.01.2023 958 D 5 17.01.2023 958 E 3

Now it looks like this:

 Player Average Score A 7 B 4.5 C 5.33 D 5.66 E 3.5 F 5.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:

 Date Player Average Score 20.01.2023 A 7 20.01.2023 B 4.5 20.01.2023 C 5.33 20.01.2023 D 5.66 18.01.2023 A 6 18.01.2023 B 5 18.01.2023 C 5.5 18.01.2023 D 5 18.01.2023 E 3.5 18.01.2023 F 5.5 17.01.2023 F 5 17.01.2023 C 6 17.01.2023 D 5 17.01.2023 E 3

2 ACCEPTED SOLUTIONS
Super User

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

Super User

this shall also work:

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

6 REPLIES 6
Super User

this shall also work:

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

Super User

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

Helper III

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?

Super User

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

Super User

@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])))

Helper III

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.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors