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 ☺️❤️
if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.
