locked
Display column values as a series/legend on a line chart where one column value can be more than one category RRS feed

  • Question

  • Hi Everyone

    I don't know if this is possible or not, so I will describe it. I have a column in a table called Filing. There are currently three values in it ("Abuse", "Neglect", "Voluntary") and each record has this populated. I also have the below slicer for the column also.

    I would like to add a fourth value called "Abuse/Neglect" that would identify instances where both an Abuse and Neglect petition was filed, which is a subset of the Abuse and Neglect records in the Filing column.

    My question is, is it possible to create a line chart that has the 4 Filing possibilities: "Abuse", "Neglect", "Voluntary", "Abuse/Neglect" as the Series/Legend in the line chart?

    The problem is that there are records that are more than one category: Abuse or Neglect records can also be Abuse/Neglect.

    Paul

    Tuesday, March 17, 2015 4:56 PM

Answers

  • Hi Paul,

    According to your description, there is a column with the values ("Abuse", "Neglect", "Voluntary") in your table, now what you need to is that add another value "Abuse/Neglect" in this column, and then use it as Series/Legend in the line chart, right?

    In this case, you can add calculated column to your table based on your logic. A calculated column is a column that you add to an existing PowerPivot table. Instead of pasting or importing values in the column, you create a DAX formula that defines the column values. The calculated column can be used in a PivotTable, PivotChart, or Power View report as you would any other data column. Please refer to the link below to see the details.
    https://msdn.microsoft.com/en-us/library/gg413492%28v=sql.110%29.aspx?f=255&MSPPError=-2147217396

    Regards,


    Charlie Liao
    TechNet Community Support

    Wednesday, March 18, 2015 5:26 AM

All replies

  • Hi Paul,

    According to your description, there is a column with the values ("Abuse", "Neglect", "Voluntary") in your table, now what you need to is that add another value "Abuse/Neglect" in this column, and then use it as Series/Legend in the line chart, right?

    In this case, you can add calculated column to your table based on your logic. A calculated column is a column that you add to an existing PowerPivot table. Instead of pasting or importing values in the column, you create a DAX formula that defines the column values. The calculated column can be used in a PivotTable, PivotChart, or Power View report as you would any other data column. Please refer to the link below to see the details.
    https://msdn.microsoft.com/en-us/library/gg413492%28v=sql.110%29.aspx?f=255&MSPPError=-2147217396

    Regards,


    Charlie Liao
    TechNet Community Support

    Wednesday, March 18, 2015 5:26 AM
  • Hi Charlie

    It is true that I need to add a fourth value "Abuse/Neglect" to the existing values in the Filing column: "Abuse", "Neglect", "Voluntary". However a calculated column won't work in this instance because the "Abuse/Neglect" value is a cross section of "Abuse" and "Neglect" filings in this same column and a value can be categorized by both: "Abuse" petitions can also be "Abuse/Neglect" and "Neglect" petitions can also be "Abuse/Neglect". And I need to capture one value as belonging to both categories.

    So I need a way to identify values as belonging to both groups (where applicable) and then have all four petition types as different values in a chart Series/Legend.

    A calculated column would need to store both categories in one field for a given record and I would need to be able to count these records as part of both separately and still be able to plot each category individually as part of a Series/Legend in a line chart.

    I don't think it is possible to do this, but it would be great if a slicer value could count instances of values in a column using its own logic.

    Paul


    • Edited by Paul-NYS Wednesday, March 18, 2015 2:17 PM
    Wednesday, March 18, 2015 2:14 PM
  • Does anyone have a suggestion for Paul for how to do this?

    Thanks!


    Ed Price, Azure & Power BI Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Thursday, April 2, 2015 6:01 PM