Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
98 | |
75 | |
63 | |
62 |
User | Count |
---|---|
135 | |
105 | |
104 | |
80 | |
65 |