skip to main content
Power BI
    • What is Power BI
    • Why Power BI
    • Customer stories
    • Data visuals
    • Security
    • Power BI Desktop
    • Power BI Pro
    • Power BI Premium
    • Power BI Mobile
    • Power BI Embedded
    • Power BI Report Server
  • Pricing
    • Azure + Power BI
    • Microsoft 365 + Power BI
    • Dynamics 365 + Power BI
      • Energy
      • Healthcare
      • Manufacturing
      • Media
      • Retail
    • For analysts
    • For IT
      • Overview
      • Embedded analytics
      • Power BI visuals
      • Automation
      • Documentation
      • Community
    • Getting started
      • Overview
      • Self-guided learning
      • Webinars
      • Documentation
      • Roadmap
      • Overview
      • Issues
      • Give feedback
    • Blog
    • Business intelligence topics
    • Overview
    • Forums
    • Galleries
    • Submit ideas
    • Events
    • User groups
    • Community blog
    • Register
    • ·
    • Sign in
    • ·
    • Help
    Go To
    • Microsoft Power BI Community
    • Welcome to the Community!
    • News & Announcements
    • Get Help with Power BI
    • Desktop
    • Service
    • Report Server
    • Power Query
    • Mobile Apps
    • Developer
    • DAX Commands and Tips
    • Custom Visuals Development Discussion
    • Health and Life Sciences
    • Power BI Spanish Community
    • Translated Spanish Desktop
    • Power Platform Integration - Better Together!
    • Power Platform Integrations
    • Power Platform and Dynamics 365 Integrations
    • Training and Consulting
    • Instructor Led Training
    • Galleries
    • Community Connections & How-To Videos
    • COVID-19 Data Stories Gallery
    • Themes Gallery
    • Data Stories Gallery
    • R Script Showcase
    • Webinars and Video Gallery
    • Quick Measures Gallery
    • 2021 MSBizAppsSummit Gallery
    • 2020 MSBizAppsSummit Gallery
    • 2019 MSBizAppsSummit Gallery
    • Events
    • Ideas
    • Custom Visuals Ideas
    • Issues
    • Issues
    • Events
    • Upcoming Events
    • Community Engagement
    • T-Shirt Design Challenge 2023
    • Community Blog
    • Power BI Community Blog
    • Custom Visuals Community Blog
    • Community Support
    • Community Accounts & Registration
    • Using the Community
    • Community Feedback
    cancel
    Turn on suggestions
    Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
    Showing results for 
    Search instead for 
    Did you mean: 
    • Microsoft Power BI Community
    • Galleries
    • Quick Measures Gallery
    • Hour Breakdown

    Hour Breakdown

    02-16-2019 09:47 AM

    Super User Greg_Deckler
    Super User
    6191 Views
    LinkedIn LinkedIn Facebook Facebook Twitter Twitter
    Greg_Deckler
    Super User Greg_Deckler
    Super User
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    Hour Breakdown

    ‎02-16-2019 09:47 AM

    This Quick Measure breaks a start and end time down into the number of minutes for each hour of the day. There are actually two measures included in order to demonstrate how to use the techniques in Measure Totals, The Final Word to display the correct totals and subtotals within a matrix.

     

     

    Hour Breakdown = 
    VAR __currentHour = HOUR(MAX('Hours'[Hour]))
    VAR __startHour = HOUR(MIN('Data'[Start]))
    VAR __endHour = HOUR(MAX('Data'[End]))
    VAR __table = GENERATESERIES(__startHour,__endHour,1)
    VAR __table1 = ADDCOLUMNS(__table,"__minutes",
        SWITCH(TRUE(),
            __startHour < __endHour && [Value] <> __endHour && [Value] <> __startHour,60,
            __startHour < __endHour && [Value] = __endHour,MINUTE(MAX('Data'[End])),
            MINUTE(MAX(Data[Start]))
        )
    )
    VAR __table2 = FILTER(__table1,[__minutes]>0)
    RETURN
    SUMX(FILTER(__table2,[Value] = __currentHour),[__minutes])

     

     

     

    Hour Breakdown Total = 
    VAR __table = SUMMARIZE('Data',[Date],[ID])
    VAR __table1 = GENERATE(__table,Hours)
    VAR __table2 = ADDCOLUMNS(__table1,"__duration",[Hour Breakdown])
    RETURN
    IF(HASONEVALUE(Hours[Hour]) && HASONEVALUE(Data[ID]),[Hour Breakdown],SUMX(__table2,[__duration]))

     

     

     

     

    eyJrIjoiYzcwMDhjZDEtM2U1Mi00MzI4LTg2NWYtMWVmZDIyNzJmODc0IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9


    @ me in replies or I'll lose your thread!!!
    Instead of a Kudo, please vote for this idea
    Become an expert!: Enterprise DNA
    External Tools: MSHGQM
    YouTube Channel!: Microsoft Hates Greg
    Latest book!:
    Mastering Power BI 2nd Edition

    DAX is easy, CALCULATE makes DAX hard...
    Preview file
    50 KB
    HourBreakdown.pbix
    Labels:
    • Labels:
    • Mathematical
    • Other
    • Time Intelligence
    • Totals
    Message 1 of 3
    6,191 Views
    1
    Reply
    • All forum topics
    • Previous Topic
    • Next Topic
    Greg_Deckler
    Super User Greg_Deckler
    Super User
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎12-20-2021 06:14 AM

    @r_kurkur Sure, if you could post that as text that would assist greatly. 


    @ me in replies or I'll lose your thread!!!
    Instead of a Kudo, please vote for this idea
    Become an expert!: Enterprise DNA
    External Tools: MSHGQM
    YouTube Channel!: Microsoft Hates Greg
    Latest book!:
    Mastering Power BI 2nd Edition

    DAX is easy, CALCULATE makes DAX hard...
    Message 3 of 3
    2,767 Views
    0
    Reply
    r_kurkur
    r_kurkur
    Regular Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎12-17-2021 07:11 AM

    Hello  @Greg_Deckler ,
    I am learning powerBI. I followed the above solution but somehow it's not working for my data. I wanted to show employee count by the hour and date. Here is what the sample data look like:

    r_kurkur_0-1639753791398.png

     

    Could you please help me? Any help on this is appreciated ‌‌

    Thanks 
    Richa

     

     

    Message 2 of 3
    2,848 Views
    0
    Reply

    Power Platform

    • Overview
    • Power BI
    • Power Apps
    • Power Pages
    • Power Automate
    • Power Virtual Agents

    • Sign in
    • Sign up

    Browse

    • Solutions
    • Consulting Services

    Downloads

    • Power BI Desktop
    • Power BI Mobile
    • Power BI Report Server
    • See all downloads

    Learn

    • Guided learning
    • Documentation
    • Support
    • Community
    • Give feedback
    • Webinars
    • Developers
    • Blog
    • Newsletter

    © 2023 Microsoft

    Follow Power BI

    • Privacy & cookies
    • Manage cookies
    • Terms of use
    • Trademarks
    Consumer Privacy Act (CCPA) Opt-Out Icon Your Privacy Choices