Forum Discussion

WILLIH's avatar
WILLIH
Helper I
6 years ago
Solved

Clustered Column Chart

I'm using a clustered column chart to show the number of open dates and the number of closed dates in each month for each line item in a table. For the axis I'm using the open date field. For the values field I'm using the open date field and close date fields. What I want is the total number of open and closed dates for each month. Currently what I'm getting is the correct number of open dates but the incorrect number of close dates. I've concluded that the reason for the incorrect number of close dates is because I'm using open date as the axis and the table is being filtered by that first. Therefore the number of closed dates is off because it's only counting what is there after the table has been filtered on the open date. Example: if you filterd the table on open dates for March and the open date total was 10 and the close date total was 6. But if you reset the table and then filter on March but using the close date you actually come up with 9 closes but the additional closes have open dates that are in months other than March. The true total of line items closed in March should be 9 and not 6.

Is there a way to get the true number for both open and closes to show up at the same time in a clustered column chart?

Need Help
  • lbendlin's avatar
    lbendlin
    6 years ago

    You want a dates/calendar table with its date on your x axis.  Create two relationships between the calendar table and your facts table. Either releationship can be inactive.  Then create measures to calculate your values, and apply USERELATIONSHIP to tell DAX which date value (open or closed) you want to compute.

  • v-diye-msft's avatar
    v-diye-msft
    6 years ago

    Hi WILLIH 

     

    First of all, Plz create a calendar table, starting from the date of the first opened ticket until present:

    Calendar = CALENDAR(FIRSTDATE('Tickets'[Open Date]),TODAY())

    Secondly,  duplicate the original 'Tickets' table,link the original 'Tickets' to 'Calendar' via a many-to-one Single relationship through 'Open Date', then linked 'Tickets Duplicate' to Calendar the same way but through 'Close Date'. I tried to use two relationships, one active and one inactive, between 'Tickets' and 'Calendar' for 'Open Date' and 'Close Date', but since only one relationship was active it wasn't able to calculate tickets-closed-per-day. That solution would only give me tickets-opened-per-day, since that was the active relationship.

     

     

    Then, create a SUMX function inside 'Tickets' and 'Tickets Duplicate' to count total tickets opened and closed, respectively: 

     

    OpenedCount = COUNT('Tickets'[Ticket Num])
ClosedCount = COUNT('Tickets Duplicate'[Close Date])

     

    Fourth, make two measures in 'Calendar' to count the total tickets opened and closed per month:

     

    OpenedSum = Calculate(SUMX('Calendar','Tickets'[OpenedCount]),VALUES('Calendar'[Date].[MonthNo]))
ClosedSum = Calculate(SUMX('Calendar','Duplicate Tickets'[ClosedCount]),VALUES('Calendar'[Date].[MonthNo]))

     

    If not help , please kindly elaborate more.