Forum Discussion

bengisby's avatar
bengisby
Icon for Helper I rankHelper I
8 years ago
Solved

clustered column chart count rows where incoming date and resolved date are 2 different bars

I feel like this has to be easy, but I just can't seem to solve it... 

 

I have in my table: Create Month, Resolved Month. team. I want to show a clustered column chart showing me, on one bar, the number created in the create Month, then in the other bar, the number resolved in the resolved month. I want to be able to select a team in the report view it updates the bars based on the team selected.

 

X axis will be the months (jan,feb,mar,etc.), then 2 bars per month, one counting tickets incoming, other counting tickets resolved.

 

Please help!

 

 

Need Help
  • MFelix's avatar
    MFelix
    8 years ago

    Hi bengisby,

     

    You need to create a calendar table and make two inactive relationships with your Created Month and Resolved Month (assuming this is date format).

     

    Then just create two measure like this:

    Created =
CALCULATE (
    COUNT ( Table[TEAM] );
    USERELATIONSHIP ( Table[Create Month]; Calendar[Date] )
)


Resolved =
CALCULATE (
    COUNT ( Table[TEAM] );
    USERELATIONSHIP ( Table[Resolved Month]; Calendar[Date] )
)

    Then add the date from the calendar table as your axis on the clustered column chart, and the measures in values.

     

    Regards,

    MFelix

5 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    8 years ago

    Hi bengisby,

     

    You need to create a calendar table and make two inactive relationships with your Created Month and Resolved Month (assuming this is date format).

     

    Then just create two measure like this:

    Created =
CALCULATE (
    COUNT ( Table[TEAM] );
    USERELATIONSHIP ( Table[Create Month]; Calendar[Date] )
)


Resolved =
CALCULATE (
    COUNT ( Table[TEAM] );
    USERELATIONSHIP ( Table[Resolved Month]; Calendar[Date] )
)

    Then add the date from the calendar table as your axis on the clustered column chart, and the measures in values.

     

    Regards,

    MFelix

    • bengisby's avatar
      bengisby
      Icon for Helper I rankHelper I
      8 years ago

      HI MFelix, I also have a table showing each ticket's details. When I click on one of the bar dates in your solution, is there a way to have the table filter to show me only the details for the list of tickets in that YYMM's bar?

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        8 years ago

         

        Not sure what you want to show in this table but you can add a new measure:

         

         

        Month_Select = [Created] + [Resolved]

         

        Then in a matrix visual the following fieds:

        • Rows: Team
        • Columns: YYYYMM (Should come from calendar table)
        • Values: Month_Select

        Other option is to create two tables with only two columns:

        • Team + Created
        • Team + Resolved

        Hide the Created and Resolved columns and this will show two columns withs the open and closed based on the graph selection.

         

        See both options below:

         

         

        Regards,

        MFelix