Forum Discussion

Phil88's avatar
Phil88
New Member
1 year ago
Solved

Normalize data ranges for Line Chart

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

Need Help
  • Rupak_bi's avatar
    Rupak_bi
    1 year ago

    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

     

     

5 Replies

  • Rupak_bi's avatar
    Rupak_bi
    Super User
    1 year ago

    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.

      • Rupak_bi's avatar
        Rupak_bi
        Super User
        1 year ago

        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

         

         

  • v-sshirivolu's avatar
    v-sshirivolu
    Community Support
    1 year ago

    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