Forum Discussion

sanman's avatar
sanman
Helper I
5 years ago
Solved

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?  
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    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

v-jingzhang's avatar
v-jingzhang
Community Support
5 years ago

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