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.
Say I have two columns, Timestamp (datetime type) and TimeSpent (duration type represented in hours or whatever).
Now i want to calculate the total TimeSpent (Sum) in % using the Timestamp field as axis, creating a hierarchy over year, quarter, month, day. It is quite easy to calculate just the sum of the duration, but then i want to divide that number with the relevant level in the hierarchy, i.e. if the navigation point in the hierarchy is "day", i want to divide the duration with 24 hours and so forth.
The closest i can get is dividing with MAX(Timestamp)-MIN(Timestamp) within the "group", however this is not optimal since i have voids in the timeline in the original table.
Any idea how to solve this?
Solved! Go to Solution.
Hey,
basically i would consider to create an additional calendar table that just contains dates.
Add an addtional column that inside your "fact" table that also just contains a date, without the time part.
Relate the tables with Calendar on the one-side and the fact table on the many-side.
Create a Measure that counts the timestamps, this measure than can be used as divisor for the sum of the durations.
This will work for a hierarchy like Day -> Month -> Quarter -> Year
If you need a more fine grained axis, than you have to build a more calendar/time table. Please be aware that you need a matching column in both of your tables, if the grain has to be an hour the values for the columns should look like this:
2018-08-14 00:00:00
2018-08-14 01:00:00
...
2018-08-14 23:00:00
2018-08-15 00:00:00
...
Hopefully this gives you an idea.
Regards,
Tom
Hey,
basically i would consider to create an additional calendar table that just contains dates.
Add an addtional column that inside your "fact" table that also just contains a date, without the time part.
Relate the tables with Calendar on the one-side and the fact table on the many-side.
Create a Measure that counts the timestamps, this measure than can be used as divisor for the sum of the durations.
This will work for a hierarchy like Day -> Month -> Quarter -> Year
If you need a more fine grained axis, than you have to build a more calendar/time table. Please be aware that you need a matching column in both of your tables, if the grain has to be an hour the values for the columns should look like this:
2018-08-14 00:00:00
2018-08-14 01:00:00
...
2018-08-14 23:00:00
2018-08-15 00:00:00
...
Hopefully this gives you an idea.
Regards,
Tom
Thank you very much for this tip.
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 |
---|---|
111 | |
95 | |
77 | |
68 | |
54 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |