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 field to create one to one relationships between the tables. I am trying to view the data by city and I want to see the change in numbers over the three tables. When I put the data into a clustered column chart one of the clusters has a (Blank) value. I suspect this is because there are rows in each data table that do not exist across all three tables. For example, there are rows in Sept that are no longer present in Jan as well as new rows in Jan that were not there in Sept.

 

How can I get new entires for Jan and Apr to appear under their apporpriate city?

 

  • 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

4 Replies

  • 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