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.
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 |
Solved! Go to Solution.
Please try
New Table =
ADDCOLUMNS (
SUMMARIZE ( 'Table', [Date], 'Table'[Player] ),
"Average Score",
CALCULATE (
AVERAGE ( 'Table'[Score] ),
ALLEXCEPT ( 'Table', 'Table'[Player] ),
[Date] <= EARLIER ( [Date] )
)
)
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
)
)
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
)
)
Hi @Pan_Forex
please try
Average Score =
CALCULATE (
AVERAGE ( 'Table'[Score] ),
ALLEXCEPT ( 'Table', 'Table'[Player] ),
[Date] <= MAX ( [Date] )
)
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?
Please try
New Table =
ADDCOLUMNS (
SUMMARIZE ( 'Table', [Date], 'Table'[Player] ),
"Average Score",
CALCULATE (
AVERAGE ( 'Table'[Score] ),
ALLEXCEPT ( 'Table', 'Table'[Player] ),
[Date] <= EARLIER ( [Date] )
)
)
@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])))
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
43 | |
19 | |
18 |