Drafting a line between two scalar values

[Anonymous]

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

[Anonymous · Accepted solution]

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

[amitchandak]

@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 · Accepted solution]

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

[FarhanAhmed]

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]

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

[FarhanAhmed]

You can use analytics pane to create trend line in visual

[Anonymous]

Trend line does not do the job. It is usually indcates a tendency of the data. I need the line to be descending from total value not from the a tendency of the data. If you can notice the snap shot for the visual I attached has a trend line above or sotimes below my total value.

[Anonymous · Accepted solution]

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]

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]

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]

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]

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

[Anonymous]

The BurningLine measure is:
BurningLine = 
VAR ProjectCost= IF(
    MIN('Calendar'[BURNDOWN CALENDAR])
        >= [Burndown Start], CALCULATE (
        SUM ( ProjectFinancials[Cost] ),
         FILTER (
        ALL ( 'Calendar'[BURNDOWN CALENDAR]),
        'Calendar'[BURNDOWN CALENDAR] <= MAX ('Calendar'[BURNDOWN CALENDAR] )
    )))
VAR _N = ProjectCost/ ( MAXX ( ALL ( 'calendar' ), 'Calendar'[Rank] ) - 1 )
RETURN
IF (
        MAX ( 'Calendar'[BURNDOWN CALENDAR]) = MINX ( ALL ( 'calendar' ), 'Calendar'[BURNDOWN CALENDAR] ),
        ProjectCost,
        ProjectCost

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