Automatically Scale My Line Chart Based on user selection

Hi eliasayyy,

I hope you are doing well ☺️❤️

So unfortunately I can't download the file but based on my understand you want to dynamically scale measures in a line chart so all lines are readable

You can try this Deneb (Vega-Lite) that automatically normalizes your data:

{
  "$schema": "https://vega.github.io/schema/vega-lite/v5.json",
  "data": {"name": "dataset"},
  "transform": [
    {
      "aggregate": [
        {"op": "sum", "field": "Impressions", "as": "Total Impressions"},
        {"op": "sum", "field": "Clicks", "as": "Total Clicks"},
        {"op": "sum", "field": "Spend", "as": "Total Spend"},
        {"op": "average", "field": "CTR", "as": "CTR"},
        {"op": "average", "field": "CPC", "as": "CPC"}
      ],
      "groupby": ["Date"]
    },
    {
      "calculate": "datum['Total Impressions']",
      "as": "Impressions_norm"
    },
    {
      "fold": [
        "Total Impressions",
        "Total Clicks",
        "Total Spend",
        "CTR",
        "CPC"
      ],
      "as": ["Measure", "Value"]
    },
    {
      "joinaggregate": [
        {
          "op": "max",
          "field": "Value",
          "as": "max_value"
        }
      ],
      "groupby": ["Measure"]
    },
    {
      "calculate": "datum.Value / datum.max_value",
      "as": "normalized_value"
    },
    {
      "calculate": "datum.Value",
      "as": "original_value"
    }
  ],
  "encoding": {
    "x": {
      "field": "Date",
      "type": "temporal",
      "title": "Date",
      "axis": {"grid": false}
    },
    "y": {
      "field": "normalized_value",
      "type": "quantitative",
      "title": "Normalized Scale (0 to 1)",
      "axis": {
        "grid": true,
        "format": ".0%",
        "title": null
      }
    },
    "color": {
      "field": "Measure",
      "type": "nominal",
      "title": "Measures",
      "scale": {"scheme": "category10"}
    },
    "tooltip": [
      {"field": "Date", "type": "temporal", "title": "Date"},
      {"field": "Measure", "type": "nominal", "title": "Measure"},
      {
        "field": "original_value",
        "type": "quantitative",
        "title": "Actual Value",
        "format": ",.0f"
      },
      {
        "field": "normalized_value",
        "type": "quantitative",
        "title": "Normalized",
        "format": ".1%"
      }
    ]
  },
  "layer": [
    {
      "mark": {
        "type": "line",
        "strokeWidth": 2,
        "interpolate": "monotone"
      }
    },
    {
      "selection": {
        "hover": {
          "type": "single",
          "on": "mouseover",
          "nearest": true,
          "empty": "none"
        }
      },
      "mark": {
        "type": "point",
        "size": 100,
        "opacity": 0
      }
    }
  ],
  "config": {
    "view": {"stroke": null},
    "axis": {
      "domainWidth": 1,
      "labelFontSize": 11,
      "titleFontSize": 13
    },
    "legend": {
      "titleFontSize": 12,
      "labelFontSize": 11,
      "symbolSize": 100
    }
  }
}

If you prefer a DAX solution create these measures:

// Base Measures (you should already have these)
Total Impressions = SUM(random_campaign_data[Impressions])
Total Clicks = SUM(random_campaign_data[Clicks])
Total Spend = SUM(random_campaign_data[Spend])
Avg CTR = AVERAGE(random_campaign_data[CTR])
Avg CPC = AVERAGE(random_campaign_data[CPC])

// Normalized Measures
Normalized Impressions = 
VAR MaxValue = MAXX(ALLSELECTED('Date'[Date]), [Total Impressions])
RETURN DIVIDE([Total Impressions], MaxValue, 0)

Normalized Clicks = 
VAR MaxValue = MAXX(ALLSELECTED('Date'[Date]), [Total Clicks])
RETURN DIVIDE([Total Clicks], MaxValue, 0)

Normalized Spend = 
VAR MaxValue = MAXX(ALLSELECTED('Date'[Date]), [Total Spend])
RETURN DIVIDE([Total Spend], MaxValue, 0)

Normalized CTR = 
VAR MaxValue = MAXX(ALLSELECTED('Date'[Date]), [Avg CTR])
RETURN DIVIDE([Avg CTR], MaxValue, 0)

Normalized CPC = 
VAR MaxValue = MAXX(ALLSELECTED('Date'[Date]), [Avg CPC])
RETURN DIVIDE([Avg CPC], MaxValue, 0)

If you want to ask any questions just mention me ☺️❤️ 

Hi eliasayyy ,

I understand und you are right that the chart looks nicer if the range of the different measures is smaller. But I wouldn't recommend to do such a scaling except your visualisations purpose is just to look good. 

Of course it looks nicer if the values are closer but the viewer of your report will think there are closer but obviously they are not. That is very misleading. 
Other options that came to my mind:

• Using a second y-axis. But I wouldn't recommend that either. The user will see values that are close but they are not.
• Using a logarithmic scale. That could work but will depend on the experience of the users. Most people are not familiar with logarithmic. And once again, you will see values close to each other that are no close to each other
• Using small multiples could really be an option. Define certain bins / classes of values. and use them to distribute your measures to these multiples. Depending on the range of their values the measures would be assigned to one of the multiples. If you choose a 1-column layout all visuals will have the same x-axis. So you can compare them
• Using normalized data could be an option as well if you are focussed on the trends. If you choose the max of all values as 100% you haven't won anything. You would have to choose the max of every line as 100% for that line. Once again users will have the impressions that values are close that are not close. In this case, you should make it very clear what is shown.

Hope that helps and that at least one of the options meets your requirements.