Forum Discussion
How to summarize a table
Hello! PBI community, I´m trying to summarize a table. I'm trying to have the next output:
In the columns, the 24 hrs of the day
In the rows, te 7 days of the week, starting in monday
In the values, the distinct count of device_id
The structure are different transactions per date/time for every device_id, so I need to see the count for every hour and day, no matter the month or year.
| 00:00 | 01:00 | 02:00 | 03:00 | 04:00 | 05:00 | 06:00 | 07:00 | |
| Monday | ||||||||
| Tuesday | ||||||||
| Wednesday | ||||||||
| Thursday | ||||||||
| Friday | ||||||||
| Saturday | ||||||||
| Sunday |
Thanks in advance for the advice!
3 Replies
what happens if you pick 2 days?
To achieve the desired output, you can create a matrix in Power BI that displays the distinct count of device_id for each hour of the day (24 hours) and each day of the week (starting from Monday). Here’s how you can set it up:
Create a New Table:
- In Power BI Desktop, go to the Model View.
- Create a new table with the following columns:
- Hour: Ranging from 00:00 to 23:00 (representing the 24 hours).
- Day: Ranging from Monday to Sunday (starting from Monday).
Create a Measure:
- Go to Modeling ➡️ New Measure.
- Enter the following DAX formula to calculate the distinct count of device_id:
Distinct Device Count = DISTINCTCOUNT('YourTable'[device_id]) - Replace 'YourTable' with the actual name of your data table.
Create a Matrix Visualization:
- Go back to the Data View.
- Create a matrix visual.
- Drag the Hour column to the Columns section of the matrix.
- Drag the Day column to the Rows section of the matrix.
- Drag the Distinct Device Count measure to the Values section of the matrix.
Format the Matrix:
- Format the matrix to display the days of the week in the desired order (starting from Monday).
- Adjust the formatting of the matrix cells as needed (e.g., font size, alignment).
Result:
- Your matrix should now show the distinct count of device_id for each hour of the day and each day of the week.
Here’s how the matrix might look (simplified example):
Hour Monday Tuesday Wednesday … Sunday00:00 10 12 15 … 8 01:00 8 11 14 … 9 … … … … … … 23:00 9 10 13 … 7 Remember to adjust the table and column names according to your actual data.
Thank you so much for the detail in your steps. I followed these steps but I don't see the same result, maybe because the way I related those tables is not the correct way to do it. I do have a field in my 'YourTable' that is StartOfHour, where I expected to make it work but it doesn't. 😞
