Forum Discussion
Challenge with my data in Column Chart
I have some data that looks like the following
| ID | Date | Opened | Closed |
| 1 | 1-Jan | 1 | 0 |
| 1 | 1-Feb | 0 | 1 |
| 2 | 1-Jan | 1 | 0 |
| 2 | 1-Feb | 0 | 1 |
| 3 | 1-Jan | 1 | 1 |
| 4 | 1-Jan | 1 | 0 |
| 4 | 1-Feb | 0 | 1 |
I want a clustered column chart that shows for each Date, a column of Opened items and a column of Closed items. Except for ID 3 in January where the item is Opened and Closed in the same month I can successfully generate the chart. Ideas for how to accomplish?
7 Replies
Please try these measure expressions in a visual with the Date column.
Open = SUMX(FILTER(Table, Table[Opened]<>Table[Closed]), Table[Opened])
Closed = SUMX(FILTER(Table, Table[Opened]<>Table[Closed]), Table[Closed])
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
The result was that it excluded all cases where Open and Closed was equal to 1, but I need them included.
I changed the filter to Table[Closed]=Table[Closed] and that worked, but seems like an odd filter. Should I do that a different way?
First time I've used a Measure, so new terratory for me. I appreciate your ideas.
And, I'm not sure how this helps me. I basically end up with the same thing I have. Two columns expressing Opened and Closed. I don't know how to then chart that into a Clustered Column Chart, showing a Column with the Opened total and a column with the Closed total, for each date.
