Forum Discussion
Executive Metrics Reporting Dashboard
Hello – amateur Power BI user here. Looking to develop a daily/weekly/monthly dashboard that details performance across various departments that looks like the below. Right now I have all these different dashboards per department that are great but want to bubble it up to show everything in 1 simple table for easy consumption.
|
| Week 1 | Week 2 |
Department 1 |
|
|
|
| Metric A |
|
|
| Metric B |
|
|
Department 2 |
|
|
|
| Metric C |
|
|
| Metric d |
|
|
Now, I know to be able to show this simply in 1 table, the data needs to be structured as 4 columns:
Department | Metric | Date | Value |
D1 | Metric A | 1/1/2023 | 1 |
D1 | Metric B | 1/2/2023 | 2 |
D2 | Metric C | 1/1/2023 | 3 |
D2 | Metric D | 1/2/2023 | 4 |
The challenge I’m facing right now is, the data for departments are recorded in numerous Excel files / tables. Is there a way to join those all together such that I can create a table like the above that will be updated as additional data/metrics are brought into the model?
Department 1 Data
Date | Metric A | Metric B |
1/1/2023 | 1 | 0 |
1/1/2023 | 0 | 0 |
1/2/2023 | 1 | 2 |
1/2/2023 | 0 | 0 |
Department 2 Data and onward are structured similarly.
Appreciate all the help here!
2 Replies
KEAlexanderHelper I
Yes, your table can update as the underlying data changes. https://learn.microsoft.com/en-us/power-bi/connect-data/service-excel-workbook-files " If you use the OneDrive for Business or SharePoint options, Power BI creates a connection to the file. As you make changes to the file in SharePoint or OneDrive, Power BI automatically syncs those changes about once an hour."
Hi Bchen
You can put the files to a folder, then combine folder in power query, you can refer to the following link.
Combine Excel Files with Power BI / Power Query — The Power User
How to Combine Files From a Folder with Excel Power Query - Xelplus - Leila Gharani
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
