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?
Need Help
  • 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. 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi RicoZohu,

Thank you!! It is wokring great!! I can get the line to descende to zero. But the way I did it the max value is less thant it should be, and I am trying to get the two lines (Burning line & TrendLine) to start at the same value as well.

Would you take a look and give me your opnion?


TrendLine =

VAR ProjectBudget= IF(

    MIN('Calendar'[BURNDOWN CALENDAR])

        >= [Burndown Start], CALCULATE (

        SUM ( ProjectFinancials[Project Budget] ),

         FILTER (

        ALL ( 'Calendar'[BURNDOWN CALENDAR]),

        'Calendar'[BURNDOWN CALENDAR] <= MAX ('Calendar'[BURNDOWN CALENDAR] )

    )))

VAR _N =ProjectBudget/ ( MAXX ( ALL ( 'calendar' ), 'Calendar'[Rank] ) - 1 )

RETURN

IF (

        MAX ( 'Calendar'[BURNDOWN CALENDAR]) = MINX ( ALL ( 'calendar' ), 'Calendar'[BURNDOWN CALENDAR] ), 

        ProjectBudget,

        ProjectBudget

            - ( SUM ( 'Calendar'[Rank] ) - 1 ) * _N

    )

 

Thanks!!

Hanna

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous 

You may try this measure:

 

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

 

You may calculate the max value in BurningLine measure in _ProjectBudget.

If this reply still couldn't help you to solve your problem, please show me your data model, or you can share your pbix file with me by your Onedrive for Business.

 

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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi RicoZhou,

    The _ProjectBudget value is coming out just right. I noticed the _A value is causing the probelm (It is bigger than it shoud). As you can see from the attached photo the Trendline is desecnding just great thanks to you!!  but it is a way below  the BurningBudget line (or ProjectBudget value). Here is the DAX for it:

    TrendLine =
    VAR ProjectBudget= IF(
        MIN('Calendar'[BURNDOWN CALENDAR])
            >= [Burndown Start], CALCULATE (
            SUM ( ProjectFinancials[Project Budget] ),
             FILTER (
            ALL ( 'Calendar'[BURNDOWN CALENDAR]),
            'Calendar'[BURNDOWN CALENDAR] <= MAX ('Calendar'[BURNDOWN CALENDAR] )
        )))
    VAR _N = ProjectBudget/ ( MAXX ( ALL ( 'calendar' ), 'Calendar'[Rank] ) -1 )
    RETURN
    IF (
           MAX ( 'Calendar'[BURNDOWN CALENDAR]) = MINX ( ALL ( 'calendar' ), 'Calendar'[BURNDOWN CALENDAR] ), 
           ProjectBudget,
           ProjectBudget
               - ( SUM ( 'Calendar'[Rank] ) - 1 ) * _N

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    I think I should add a filter to the calendar'[Rank]. I tried this but I got a way bigger value than the ProjectBudget. I guess the filter is not working. Do you have any thoughts? Here is the DAX for VAR _N  :
    VAR _N = ProjectBudget/ ( MAXX(FILTER('Calendar','Calendar'[Rank]>[Burndown Start] && 'Calendar'[Rank]<[Burndown End]), 'Calendar'[Rank] ) - 1)

     

     

    Thanks!!

    Hanna