cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
newbieuser
Helper I
Helper I

Creating a summary measure

newbieuser_0-1622708818406.png

 

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,

1 ACCEPTED SOLUTION
ERD
Solution Sage
Solution Sage

Hi @newbieuser ,

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

ERD_0-1623064590925.png

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
ERD
Solution Sage
Solution Sage

Hi @newbieuser ,

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

ERD_0-1623064590925.png

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

View solution in original post

Brilliant. Thanks!

V-pazhen-msft
Community Support
Community Support

@newbieuser 

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.

AlB
Super User III
Super User III

Hi @newbieuser 

Just place Date in a table visual and add this measure to the visual:

Measure = SUM ( Table1[Total Time (mins)])  

 

SU18_powerbi_badge

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.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors