How to summarize a table

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:

1. 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).

2. 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.

3. 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.

4. 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).

5. 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):

Remember to adjust the table and column names according to your actual data.