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
Super User
Super User

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
Super User
Super User

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
Super User

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
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!