Forum Discussion

mazzurri's avatar
mazzurri
Regular Visitor
6 years ago
Solved

Stacked Bar Graph from time duration column

I am currently capturing my team's  daily activity. I have a query for time duration of each activity (end time - start time). Right now I am plotting total duration (00:00 - 23:59). I would like to filter so that I have duration from 00:00 - 11:59 as the day shift and 12:00 to 23:59 as the night shift. I would like them to be stacked bar graphs as well. Would someone be able to provide me insight on how to go about solving this?

 

The bar graph is currently showing total duration and the line graph is showing total logged ticket count for the day. I would like to be able to filter them for the day/night shift and have them on the same graph.

 

Thank you

 

Mario

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi mazzurri 

    For your case, you could try this way:

    Create two day /night measure for Duration and  two day /night measure for Ticket Count

    For example:

    DAY shift Duration = CALCULATE(SUM('Table 1'[Duration]),FILTER('Table 1','Table 1'[SHIFTCOLUMN]="DAY shift" ))
    Night shift Duration = CALCULATE(SUM('Table 1'[Duration]),FILTER('Table 1','Table 1'[SHIFTCOLUMN]="Night shift" ))
     
    Day shift Ticket Count = CALCULATE(SUM('Table 1'[Ticket Count]),FILTER('Table 1','Table 1'[SHIFTCOLUMN]="Day shift" ))
    Night shift Ticket Count = CALCULATE(SUM('Table 1'[Ticket Count]),FILTER('Table 1','Table 1'[SHIFTCOLUMN]="Night shift" ))
     
    Then put these measrue into visual
    Result:
    BeforeAfter
     
    and here is sample pbix file, please try it.
     
    Regards,
    Lin

4 Replies

  • sturlaws's avatar
    sturlaws
    Resident Rockstar
    6 years ago

    Hi, mazzurri 

     

    without knowing your data, I can only do an estimated guess on what may solve your problem:

    create a new calculated like this:

    column =
IF (
    table[start time] >= TIME ( 0, 0, 0 )
        && table[start time] <= TIME ( 11, 59, 0 ),
    "Day shift",
    "Night shift"
)

     

    It seems like you are quite new here in the forum, welcome. Here is a link to a post with a lot of tips for how to getting high quality answers when you post in this forum: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    Cheers,
    Sturla

    If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

    • mazzurri's avatar
      mazzurri
      Regular Visitor
      6 years ago

      Thank you! That helps in getting a "shift" filter on my plot, but how would I plot them both on the same bar/line graph? I would like 2 lines (1 for day and 1 for night) and a stacked bar graph for duration (day and night stacked on each other).

       

      • v-lili6-msft's avatar
        v-lili6-msft
        Community Support
        6 years ago

        hi mazzurri 

        For your case, you could try this way:

        Create two day /night measure for Duration and  two day /night measure for Ticket Count

        For example:

        DAY shift Duration = CALCULATE(SUM('Table 1'[Duration]),FILTER('Table 1','Table 1'[SHIFTCOLUMN]="DAY shift" ))
        Night shift Duration = CALCULATE(SUM('Table 1'[Duration]),FILTER('Table 1','Table 1'[SHIFTCOLUMN]="Night shift" ))
         
        Day shift Ticket Count = CALCULATE(SUM('Table 1'[Ticket Count]),FILTER('Table 1','Table 1'[SHIFTCOLUMN]="Day shift" ))
        Night shift Ticket Count = CALCULATE(SUM('Table 1'[Ticket Count]),FILTER('Table 1','Table 1'[SHIFTCOLUMN]="Night shift" ))
         
        Then put these measrue into visual
        Result:
        BeforeAfter
         
        and here is sample pbix file, please try it.
         
        Regards,
        Lin