Forum Discussion
How to create Clustered Column chart with multiple year dimensions
I want to show the number of items created by year and number of items closed by year on the same clustered column chart. How can I do that?
Hi sanman
Both of the above solutions can successfully achieve what you want. I illustrate both in my understanding with images below in case you need it.
Solution 1:
1. In query editor, select CreateYear and ClosedYear columns and unpivot them.
2. Create a clustered column chart like below.
Solution 2:
1. Create a year table, then create an active relationship ‘Items (2)’CreateYear(*) – ‘Year’Year(1) and an inactive relationship ‘Items (2)’ClosedYear(*) – ‘Year’Year(1).
2. Create a measure using Userelationship function as below.
Count of Closed = CALCULATE(COUNTROWS('Items (2)'),USERELATIONSHIP('Items (2)'[ClosedYear],'Year'[Year]))3. Create the clustered column chart.
Best Regards,
Community Support Team _ Jing Zhang
3 Replies
sanman - I *think* you need to unpivot your year columns. Use Attribute as the legend, Value as the axis. Count of itemname for value
sanman , Create a year table join it with both Create year and close year. One will active another one will be inactive. Use userelation to in the measure to change date
This video has an HR example - but applies here too -
Refer
Hi sanman
Both of the above solutions can successfully achieve what you want. I illustrate both in my understanding with images below in case you need it.
Solution 1:
1. In query editor, select CreateYear and ClosedYear columns and unpivot them.
2. Create a clustered column chart like below.
Solution 2:
1. Create a year table, then create an active relationship ‘Items (2)’CreateYear(*) – ‘Year’Year(1) and an inactive relationship ‘Items (2)’ClosedYear(*) – ‘Year’Year(1).
2. Create a measure using Userelationship function as below.
Count of Closed = CALCULATE(COUNTROWS('Items (2)'),USERELATIONSHIP('Items (2)'[ClosedYear],'Year'[Year]))3. Create the clustered column chart.
Best Regards,
Community Support Team _ Jing Zhang
