Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Linear Reference Line from zero to a given target value

  Hello All, how can I insert a linear trendline from zero to a pre-defined (max) value? Lines are: green: running total last year blue: running total this year red: target for this year ...
Show and Tell
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi Anonymous,

     

    You can write a measure to calculate out correspond value as 'reference line', then drag it to value field to achieve your requirement:

    Reference = 
VAR _current =
    SELECTEDVALUE ( 'Sample'[Index] )
VAR _min = 0
VAR _max =
    MAXX ( ALLSELECTED ( 'Sample' ), [Amount] )
RETURN
    IF (
        _current = MINX ( ALLSELECTED ( 'Sample'[Index] ), [Index] ),
        0,
        _current * _max
            / COUNTROWS ( ALLSELECTED ( 'Sample' ) )
    )

     

    If you still confused on coding formula, please share some sample data for test.


    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Anonymous,

 

You can write a measure to calculate out correspond value as 'reference line', then drag it to value field to achieve your requirement:

Reference = 
VAR _current =
    SELECTEDVALUE ( 'Sample'[Index] )
VAR _min = 0
VAR _max =
    MAXX ( ALLSELECTED ( 'Sample' ), [Amount] )
RETURN
    IF (
        _current = MINX ( ALLSELECTED ( 'Sample'[Index] ), [Index] ),
        0,
        _current * _max
            / COUNTROWS ( ALLSELECTED ( 'Sample' ) )
    )

 

If you still confused on coding formula, please share some sample data for test.


Regards,

Xiaoxin Sheng

  • rshifflet's avatar
    rshifflet
    Frequent Visitor
    7 years ago

    I am having the same issue. I have a cumulative measure that adds revenue over time, however I am trying to add a linear reference line from 0 (zero) at the beginning of the year, to the target (indicated as a SUM measure), at the end of the year. I am able to get the formula to give a strait horizontal line at zero, and I can set it a the target, but I am unable to get it to Return a line from zero to my target mesure. Could you explain the formula a bit further?

     

    Aaron