I am starting to collect some new data for my organization, but need a better way to organize it, especially for the purposes of creating a dashboard in PowerBI. I am also a newbie to PowerBI.
How my previous bad habits began:
- Collected data for only one location
- Kept different kinds of data on different sheets - most notably, while most of my data was monthly, I had some that was quarterly, and a few that were annual which therefore were each kept on different sheets
Problems I am facing now:
- Now am working with data from multiple locations (will need to compare them to each other + home location)
- Am assuming that I will need to combine the data measured at different intervals into one sheet somehow... But is that true? and if so, how? If it is not necessary, how do I connect these data together for the purpose of building charts in PowerBI, such as creating a clustered column chart that shows the Q1 data of one data type + monthly data of another related data type together for each location
How my prototype dashboard data is organized:
- Currently only have one point-in-time captured for each column (so for example, Column C only has January 2024 data, while Column D only has Q1 2024 data).
- Location rows
- One for each city, plus one for home state average and one for national average (Much of this is from external data sources, such as Cost of Living Index data, so it is not something that I can just average from my other data)
- For the cities, State is also then listed in the second column (Home state and National average have "null" in those cells).
Some of what I am assuming needs to be done:
- I will need to have a date column first, and then a location column, followed by all other data columns
- Would it work to have a couple of columns with some sort of mechanism built in just for filtering quarterly/annual data? e.g. Annual data gets 1/1/2023 in the first column and then gets an "annual" label in a separate column- or Q2 data gets the date 4/1/2023 and some sort of quarterly label in a separate column
How off base am I? What would be the best way for me to organize this data going forward? Is there anything else that I am missing?
