The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
I have a table in the above format. Now total minutes in a day is 1440.
So what I want to do is sum the Total Time (mins) per day and then calculate that sum/1440 as a percentage - which will be a measure.
The results would be
1st Jan = (23+45+34)/1440
2nd Jan = (33/1440)
3rd Jan = (44+56) / 1440
So then if I use a date slicer in a visual then for any range of dates the measure returns the net percentage for those dates.
Also, the total time is a calculated column so "Group By" doesn't work!!
I even tried to do a slicer range to calculate the number of days and then multiply the number of days with 1440. Then sum the minutes and divide it by the gross but that doesn't work!
Please help!
Thanks,
Solved! Go to Solution.
Hi @Anonymous ,
This is the part I was able to understand:
The results would be
1st Jan = (23+45+34)/1440
2nd Jan = (33/1440)
3rd Jan = (44+56) / 1440
Which can be achieved bt a measure:
Measure =
VAR currentDate = MAX ( T[Date] )
RETURN
CALCULATE (
SUM ( T[Mins] ) / 1440,
T[Date] = currentDate,
ALLEXCEPT ( T, T[Date] )
)
If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
Hi @Anonymous ,
This is the part I was able to understand:
The results would be
1st Jan = (23+45+34)/1440
2nd Jan = (33/1440)
3rd Jan = (44+56) / 1440
Which can be achieved bt a measure:
Measure =
VAR currentDate = MAX ( T[Date] )
RETURN
CALCULATE (
SUM ( T[Mins] ) / 1440,
T[Date] = currentDate,
ALLEXCEPT ( T, T[Date] )
)
If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
Brilliant. Thanks!
@Anonymous
Please create the following measure:
Measure = Calculate(sum([Total Time(mins)]), filter(allselected(table),[date]=max([date]))) / Calculate(sum([Total Time(mins)]), All(table))
Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
That gives total time as percentage of the sum of time in the table. Not sure whether that is what I am looking for! Just need a conditional sort of sum as asked. Thanks.
Hi @Anonymous
Just place Date in a table visual and add this measure to the visual:
Measure = SUM ( Table1[Total Time (mins)])
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
Oh that doesn't work. Sorry I should have been more specific. I will detail the problem and edit the original post. My apologies.
User | Count |
---|---|
160 | |
110 | |
96 | |
86 | |
75 |
User | Count |
---|---|
159 | |
136 | |
133 | |
81 | |
61 |