Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Anonymous
Not applicable

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

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.

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!

View solution in original post

6 REPLIES 6
ERD
Super User
Super User

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

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.

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!

Anonymous
Not applicable

Brilliant. Thanks!

V-pazhen-msft
Community Support
Community Support

@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.

Anonymous
Not applicable

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 @Anonymous 

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.

 

Anonymous
Not applicable

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.