How to summarize a table

[adriancho_BI]

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!

[AnalyticsWizard]

@adriancho_BI 

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

Hour Monday Tuesday Wednesday … Sunday

00: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.

[adriancho_BI]

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

[bsheffer]

what happens if you pick 2 days?