Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi
I have some sample data as I cannot share my actual data:
A table of visits by 6x different people from 3x different companies with their performance.
I am trying to display a visual such as a matrix/ table where if the end-user picks from a slicer a Person, the visual will show me the following:
I understand I will need to create two measures, but not sure how to get a rolling average for one column by the last 6 times it is in the data and another for the rolling average of a company by the last 3 months.
Would appreciate the help.
Data looks like below and actual sheet is available but not sure how to attach it to this post:
Date | Time | Name | Company | Performance |
1-Jul-18 | 18:18 | John Smith | PST Ltd | 33.20 |
1-Jul-18 | 21:05 | Gary Peters | PST Ltd | 31.77 |
2-Jul-18 | 7:01 | Nicole George | 123 Solutions | 32.95 |
2-Jul-18 | 22:34 | Clarisse Nathan | 123 Solutions | 29.63 |
3-Jul-18 | 7:24 | Terry Edwards | MPI Corp | 32.06 |
3-Jul-18 | 11:42 | Cory Brewster | MPI Corp | 33.00 |
5-Jul-18 | 8:47 | John Smith | PST Ltd | 31.13 |
5-Jul-18 | 18:09 | Gary Peters | PST Ltd | 33.64 |
6-Jul-18 | 1:30 | Nicole George | 123 Solutions | 32.85 |
6-Jul-18 | 8:22 | Clarisse Nathan | 123 Solutions | 36.92 |
6-Jul-18 | 11:37 | Terry Edwards | MPI Corp | 28.43 |
7-Jul-18 | 1:45 | Cory Brewster | MPI Corp | 31.54 |
8-Jul-18 | 18:23 | John Smith | PST Ltd | 33.20 |
8-Jul-18 | 22:24 | Gary Peters | PST Ltd | 31.59 |
9-Jul-18 | 7:51 | Nicole George | 123 Solutions | 32.46 |
10-Jul-18 | 4:29 | Clarisse Nathan | 123 Solutions | 32.11 |
10-Jul-18 | 10:01 | Terry Edwards | MPI Corp | 34.24 |
12-Jul-18 | 6:00 | Cory Brewster | MPI Corp | 25.08 |
12-Jul-18 | 11:26 | John Smith | PST Ltd | 27.31 |
12-Jul-18 | 18:54 | Gary Peters | PST Ltd | 36.41 |
13-Jul-18 | 5:40 | Nicole George | 123 Solutions | 38.06 |
13-Jul-18 | 8:11 | Clarisse Nathan | 123 Solutions | 33.64 |
13-Jul-18 | 11:36 | Terry Edwards | MPI Corp | 28.29 |
14-Jul-18 | 2:23 | Cory Brewster | MPI Corp | 29.78 |
14-Jul-18 | 18:17 | John Smith | PST Ltd | 42.97 |
15-Jul-18 | 9:15 | Gary Peters | PST Ltd | 27.36 |
15-Jul-18 | 12:25 | Nicole George | 123 Solutions | 27.80 |
16-Jul-18 | 2:11 | Clarisse Nathan | 123 Solutions | 30.13 |
16-Jul-18 | 8:55 | Terry Edwards | MPI Corp | 30.15 |
17-Jul-18 | 8:42 | Cory Brewster | MPI Corp | 32.53 |
18-Jul-18 | 21:39 | John Smith | PST Ltd | 39.19 |
19-Jul-18 | 4:01 | Gary Peters | PST Ltd | 31.60 |
19-Jul-18 | 13:49 | Nicole George | 123 Solutions | 30.73 |
19-Jul-18 | 18:40 | Clarisse Nathan | 123 Solutions | 37.95 |
20-Jul-18 | 7:46 | Terry Edwards | MPI Corp | 32.07 |
20-Jul-18 | 11:06 | Cory Brewster | MPI Corp | 28.93 |
Solved! Go to Solution.
Hi @VictorR,
Please check the following steps.
1. Create a dimtime table and create relationship between fact table and date table.
dimtime = CALENDARAUTO()
2. Create the measures as below.
latest performance = var maxdate = CALCULATE(MAX(Table1[Date]),ALLSELECTED(Table1)) return CALCULATE(MAX(Table1[Performance]),FILTER(Table1,Table1[Date]=maxdate))
average performance = AVERAGEX( TOPN(6,Table1,Table1[Date],ASC),Table1[Performance])
Company's Average performance = CALCULATE(SUM(Table1[Performance]),DATEADD(dimtime[Date],-3,MONTH))/CALCULATE(COUNTROWS(Table1),DATEADD(dimtime[Date],-3,MONTH))
For more details, please check the pbix as attached.
Regards,
Frank
Hi @VictorR,
Please try to open this file.
Regards,
Frank
Hi @VictorR,
Please check the following steps.
1. Create a dimtime table and create relationship between fact table and date table.
dimtime = CALENDARAUTO()
2. Create the measures as below.
latest performance = var maxdate = CALCULATE(MAX(Table1[Date]),ALLSELECTED(Table1)) return CALCULATE(MAX(Table1[Performance]),FILTER(Table1,Table1[Date]=maxdate))
average performance = AVERAGEX( TOPN(6,Table1,Table1[Date],ASC),Table1[Performance])
Company's Average performance = CALCULATE(SUM(Table1[Performance]),DATEADD(dimtime[Date],-3,MONTH))/CALCULATE(COUNTROWS(Table1),DATEADD(dimtime[Date],-3,MONTH))
For more details, please check the pbix as attached.
Regards,
Frank
Hi Frank,
How are you narrowing down the Average in your Company's Average Performance measure by Company?
Also apologies I cannot open your .pbix file due to version differences.
My company's IT have set us all up on version: 2.56.5023.1021
Hi @VictorR,
Please try to open this file.
Regards,
Frank
Hi @VictorR,
Does that make sense? If so, kindly mark my answer as a solution to close the case.
Regards,
Frank
Hi @v-frfei-msft,
I was able to use your formula foir the last 6x visits and average performance. However, the Company's last three months' average is still not correct.
Your formula isn't referencing/ filtering down by company.
I think I will be better off doing this in SQL instead of within PowerBI and then import the query into the dashboard.
Thank you for your help and I will mark your response as a solution to close this case.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.