Forum Discussion

jpaplanus's avatar
jpaplanus
New Member
8 years ago
Solved

(Blank) in clustered column chart

I have three tables providing resource data, all providing the same information from three different time periods (Sept, Jan, Apr). Each table contains a field that has a unique ID. I am using this f...
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi jpaplanus,

     

    You can add a Month No column in each table. 

    Sep Table: New Column="Sep"

    Jan Table: New Column="Jan"

    Apr Table: New Column="Apr"

     

    However, if each table contains a date column, the new calculated column could be New Column=Month(TableName[Date])

     

    Then, Union above three tables. When you create the clustered column chart, place [New Column] into Legend section, add [City] to X-axis, and add [Value] to "Values".

     

    Best regards,

    Yuliana Gu

v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi jpaplanus,

 

Since the (Blank) means there existing some rows in Sept that are no longer present in Jan, why don't you add the [City] column from table 'Sept' rather than table 'Jan'? Please compare the difference from below screenshot.

 

Alternatively, you could combine these three tables as a single one via UNION, if they share the same table structure.

 

Regards,

Yuliana Gu

jpaplanus's avatar
jpaplanus
New Member
8 years ago

v-yulgu-msft,

 

Thank you for the reply! My chart is already using the [City] column from the Sept table. I start with that one and then add in the other two data tables but it still gives me the (Blank)s. The UNION does bring the two together, but I still need to see each quarter in its own bar as part of the cluster.

 

In the image I provided, the teal is Sept, black is Jan and orange is Apr. I apologize if I should know this, but is there a way to use UNION but still have each month be its own bar in each city cluster?

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi jpaplanus,

     

    You can add a Month No column in each table. 

    Sep Table: New Column="Sep"

    Jan Table: New Column="Jan"

    Apr Table: New Column="Apr"

     

    However, if each table contains a date column, the new calculated column could be New Column=Month(TableName[Date])

     

    Then, Union above three tables. When you create the clustered column chart, place [New Column] into Legend section, add [City] to X-axis, and add [Value] to "Values".

     

    Best regards,

    Yuliana Gu

    • jpaplanus's avatar
      jpaplanus
      New Member
      8 years ago

      v-yulgu-msft, thank you for the additional idea of adding in the month column. My data did not have a date field as it was not needed, but adding in Sept 2017, Jan 2018 and Apr 2018 did the trick. After creating the UNION your recommendation on which data fields to apply to which section lined it up perfectly for me!