Normalize data ranges for Line Chart

[Phil88]

Hi all,

i have four monetary datasets A B C D on a timeline that range between some thousands to millions and want to bring them together in a single line chart just to see the trends. By default lines are pretty straight (picture bottom left) because of the wide ranges and i wish them to be as "curvy" as in my manipulated picture bottom right. I guess i have to bring the values to a common scale by measure but can't wrap my head around the calculations as the factors have to be dynamically? No need to show y-axis but absolute values should be available on mouseover. Any help is appreciated

[v-sshirivolu]

Hi @Phil88 ,
Thanks for reaching out to the Microsoft fabric community forum.


Try Min-Max Normalization by Measure and Time

This technique rescales each dataset (A, B, C, D) to a range of 0 to 1 over the selected time window

Create a Normalized Measure for Each
A_Normalized =
VAR MinA = CALCULATE(MIN('Data'[A]), ALLSELECTED('Data'))
VAR MaxA = CALCULATE(MAX('Data'[A]), ALLSELECTED('Data'))
RETURN
DIVIDE('Data'[A] - MinA, MaxA - MinA)

Repeat similarly for B, C, and D.

Then After :
Use a unpivoted format (e.g., columns: Date, MeasureName, Value, NormalizedValues) in your data model.Use NormalizedValue as your Y-axis in the line chart, and Measure Name as legend.Add a tooltip or use Tooltips page to show the original value on hover.

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it

Best Regards,
Sreeteja.
Community Support Team 

[Rupak_bi]

hi @Phil88 ,

You can scale them from 0 to 100 and plot in a single chart. 

You need to create separate measures for each lines as per below syntax
measure = 

var min_val = calculate(min(table , value),allselected(table, X-axix column))

var max_val = calculate(max(table , value),allselected(table, X-axix column))

var current_val = max(table,value)

var range_val = max_val - min_val

return

divide(current_val,range_val)

this should work. plz share sample data for exact representation. 

Now to see the actual value, in the data label section, you can refer the respective columns.

[Phil88]

Thank you but unfortunately that measure didn't come out as expected, see sample file:

https://drive.google.com/file/d/1V0veiEvRafamszY5CYunGokksV-FcTS7/view?usp=sharing

[Rupak_bi · Accepted solution]

Hi @Phil88 ,

Thanks for sharing the sample file. This helped me to understand my mistake. please modify the measure as below

Linie_A = 
var min_val = calculate(min(Tabelle[A]),allselected(Tabelle[Datum]))
var max_val = calculate(max(Tabelle[A]),allselected(Tabelle[Datum]))
var current_val = max(Tabelle[A])
var factor = 100/(max_val - min_val)
return
(current_val-min_val)*factor

Please refer the last graph .

If this solves,  Plz accept as solution

[Phil88]

Thanks, works perfect