Custom Sorted Bars Chart

Example

Illustration

Summary of settings

  • Type of chart: Bars
  • Mapping of y attribute: City
  • Mapping of x attribute: Profit
  • Cities are sorted by profit
  • Color is mapped to a formula, returning 1 and 0 for positive and negative Profit

Step-by-step instruction

  1. Add new Chart Widget.

  2. Select Bars.

  3. If there are multiple cubes connected to your application, you must select the cube you want for the chart.

  4. To map y attribute, select members of a dimension you would like to see on the vertical axis, in this case City.

  5. To map x attribute, select the value (measure) corresponding to the horizontal axis, in this case Profit.

    The chart displays with axes mapped.

    Illustration

  6. To sort the chart by Profit, click the sort icon next to the axis label:

    Illustration

  7. Assign colors to bars. Generate values for the color scale:

    • 0 for Profit within 8000 in either direction of 0
    • -1 for Profit below -8000
    • 1 for Profit above 8000

    In the Options popup (from Legend) find the field Color and map it to a formula:

 CASE
   WHEN [Measures].[pnlVega.SUM] > 8000 THEN 1
   WHEN [Measures].[pnlVega.SUM] < - 8000 THEN - 1
   ELSE 0
 END

Illustration

Illustration

  1. Center the color scale to preserve coloring if all values ever change to only positive or only negative. In the Options popup, click the measure name in the Color field. Select Options. Make sure the box for Center is checked.

Illustration

Now the colors will always stay the same even when all values are negative. Example:

Illustration

Back to Legacy Chart Gallery

Appendix

The appendix contains code snippets for advanced users.

MDX:

WITH
 Member [Measures].[3 colors] AS CASE
  WHEN [Measures].[pnlVega.SUM] > 8000 THEN 1
  WHEN [Measures].[pnlVega.SUM] < - 8000 THEN - 1
  ELSE 0
END, FORMAT_STRING = "#,###.##"
SELECT
  NON EMPTY Order(
    [Geography].[City].[City].Members,
    [Measures].[pnlVega.SUM],
    BASC
  ) ON ROWS,
  {
    [Measures].[pnlVega.SUM],
    [Measures].[3 colors]
  } ON COLUMNS
  FROM [EquityDerivativesCube]

JSON:

{
  "configurations": [
    {
      "handlers": {
        /* ... */
      },
      "type": "combo-horizontal-histogram",
      "mapping": {
        "y": {
          "from": ["[Geography].[City].[City]"]
        },
        "x": {
          "from": "[Measures].[Profit]"
        },
        "color": {
          "from": "[Measures].[Color Coding]",
          "center": true
        }
      }
    }
  ]
}