Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Drafting a line between two scalar values

How can I get a line drafted between two scalar values that were generated from two different measures?

  • Anonymous's avatar
    Anonymous
    6 years ago

    I am trying to get a line like the trend line. The line should be descending from a total value all the way to zero.
    Right now, the line is showing as constant line at the total value. Here is my measure:

     

    BurningLine =


    VAR ProjectStartDate = CALCULATE(FIRSTDATE(Projects[Baseline Start Date]),ALLSELECTED(Projects[Baseline Start Date]))
    VAR ProjectEndDate = CALCULATE(MAX(Projects[Baseline End Date]),ALLSELECTED(Projects[Baseline End Date]))
    VAR BaseLine= DATEDIFF(ProjectStartDate,ProjectEndDate,day)

        VAR ProjectBudget = CALCULATE (
            SUM ( ProjectFinancials[Project Budget] ),
             FILTER (
            ALL ( 'Calendar'[BURNDOWN CALENDAR]),
            'Calendar'[BURNDOWN CALENDAR] >= MIN ('Calendar'[BURNDOWN CALENDAR] )
        ))


    VAR DailyBurn =DIVIDE( ProjectBudget,BaseLine )

    VAR BLine = ProjectBudget - DailyBurn


    RETURN
    BLine

  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous 

    You can try to build a trend line measure, and change the color to white then build a trend line in analytics pane.

    Default:

    Add a rank column in calendar table:

     

    Rank = RANKX('calendar','calendar'[Date],,ASC)

     

    Trend line measure:

     

    TrendLine = 
    VAR _A =
        MAXX ( ALL ( 'Table' ), [Measure 2] )
            / ( MAXX ( ALL ( 'calendar' ), 'calendar'[Rank] ) - 1 )
    VAR _Maxmeasure = MAXX(ALL('Table'),[Measure 2])
    RETURN
        IF (
            MAX ( 'calendar'[Date] ) = MINX ( ALL ( 'calendar' ), 'calendar'[Date] ),
            _Maxmeasure,
            _Maxmeasure
                - ( SUM ( 'calendar'[Rank] ) - 1 ) * _A
        )

     

    New result:

    If this reply still couldn't help you, please show me more details. You may show me your sample data model, or you can share your pbix file with me by your Onedrive for Business.

    You can download the pbix file from this link: Drafting a line between two scalar values

     

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

12 Replies

  • FarhanAhmed's avatar
    FarhanAhmed
    Community Champion

     

    Can you please share screenshot of what you want so that we can understand it better?

     

    In meanwhile why can't you take the Average of 2 measures and create a thrid line out of it 

    • Anonymous's avatar
      Anonymous
      Not applicable

      Screenshot should look like a trend line descending from a total value all the way to zero.

      • FarhanAhmed's avatar
        FarhanAhmed
        Community Champion

        You can use analytics pane to create trend line in visual

  • Anonymous ,The information you have provided is not making the problem clear to me. Can you please explain with an example.

    Appreciate your Kudos.

    • Anonymous's avatar
      Anonymous
      Not applicable

      I am trying to get a line like the trend line. The line should be descending from a total value all the way to zero.
      Right now, the line is showing as constant line at the total value. Here is my measure:

       

      BurningLine =


      VAR ProjectStartDate = CALCULATE(FIRSTDATE(Projects[Baseline Start Date]),ALLSELECTED(Projects[Baseline Start Date]))
      VAR ProjectEndDate = CALCULATE(MAX(Projects[Baseline End Date]),ALLSELECTED(Projects[Baseline End Date]))
      VAR BaseLine= DATEDIFF(ProjectStartDate,ProjectEndDate,day)

          VAR ProjectBudget = CALCULATE (
              SUM ( ProjectFinancials[Project Budget] ),
               FILTER (
              ALL ( 'Calendar'[BURNDOWN CALENDAR]),
              'Calendar'[BURNDOWN CALENDAR] >= MIN ('Calendar'[BURNDOWN CALENDAR] )
          ))


      VAR DailyBurn =DIVIDE( ProjectBudget,BaseLine )

      VAR BLine = ProjectBudget - DailyBurn


      RETURN
      BLine