Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
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:
Create a Measure:
Distinct Device Count = DISTINCTCOUNT('YourTable'[device_id])
Create a Matrix Visualization:
Format the Matrix:
Result:
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. 😞
what happens if you pick 2 days?
User | Count |
---|---|
97 | |
88 | |
76 | |
70 | |
63 |
User | Count |
---|---|
112 | |
96 | |
95 | |
67 | |
65 |