Create a Dynamic Reference Line Based on A Cumulative Measure

[nxsjd]

Hi! 

I need to create a calculated measure which will serve as a reference line in my line chart. This reference line should be based on another calculated measure (Cumulative Target) which is a cumulative sum of my Target column. Here's my formula for the cumulative target: 

Cumulative Target = 
    CALCULATE( SUM( 'Table'[Target] ),
               FILTER( ALLSELECTED( 'Table' ),
                       'Table'[Month Order] <= MAX( 'Table'[Month Order] )
               ))

I want my reference line measure to be a constant which is the cumulative sum at a specific month. For example, if I want a reference line for April then my line should be y=14 or 14 all throughout the months. Is there a way to create this measure?

Thank you!

                                                                                     Sample of Desired Chart

Here's the link to the pbix file Dynamic Reference Measure

[PaulDBrown · Accepted solution]

@nxsjd

Hi,

Here is your PBIX file with the relevant corrections and additions to make the dynamic reference line. A couple of notes:
1. to calculate the cumulative values you need to wrap the 'Table" in ALL instead of ALLSELECTED.

2. I've created two examples:

       - one with a fixed reference line (April Example)

       - A dynamic Reference Line example

3. To be able to let the user select the reference month, you need to create a disconnected table to use as the slicer. You will see this in the relationships pane.

Hope this helps!

Regards,
Paul.

PS: Link to the file

[PaulDBrown · Accepted solution]

@nxsjd

Hi,

Here is your PBIX file with the relevant corrections and additions to make the dynamic reference line. A couple of notes:
1. to calculate the cumulative values you need to wrap the 'Table" in ALL instead of ALLSELECTED.

2. I've created two examples:

       - one with a fixed reference line (April Example)

       - A dynamic Reference Line example

3. To be able to let the user select the reference month, you need to create a disconnected table to use as the slicer. You will see this in the relationships pane.

Hope this helps!

Regards,
Paul.

PS: Link to the file

[nxsjd]

@PaulDBrown

Thanks Paul! But is this going to work if I have a more complicated table, for example, I have another column specifying a type like this? 

[PaulDBrown]

@nxsjd

There is another way of doing this (which is what I normally do, and actually got me confused when tackling your table!), which involves creating another Month table, but this as a lookup table for your main table. (as well as the disconnected table used for the constant dynamic target line).

This other month table has a one-to-many relatonship with your main table and is what is used for cumulative measures and as the axis for graphs etc.. Similarly I would also create lookup tables for your "Type" columns, and establish a relationship with the main table. All lookup tables are then used in the measures and as slicers etc (see how in the relationships pane.)....IMO it makes the model much easier to understand and to manage. 

The measures change in this model (you can see how they are written since I've added 'ALL' at the end of these). 

Here is the file, if you're interested.

Regards,

Paul.

[nxsjd]

@PaulDBrown

Well, I didn't get to consider this as I'm just still learning what could be the best practices in Power BI and DAX. (Not an Excel power user also ) I'll use lookup tables from now on. Can you recommend good materials for learning Power BI + DAX?

Anyway, this is really a big help to me. Thanks a lot Paul! 

[PaulDBrown]

@nxsjd

I'm happy to be of some help (limited to my knowledge). I'm still learning too!  

There are many excellent resources to help you along with DAX and Power BI. I personally have invested in some books:

https://www.amazon.com/Learn-Write-DAX-practical-learning/dp/1615470417/ref=sr_1_3?ie=UTF8&qid=1498732571&sr=8-3&keywords=Power+Pivot

https://www.amazon.com/Definitive-Guide-DAX-intelligence-Microsoft/dp/073569835X/ref=sr_1_1?ie=UTF8&qid=1513249185&sr=8-1&keywords=Definitive+Guide+to+daxhttps://www.amazon.com/Power-Pivot-BI-Excel-2010-2016/dp/1615470395/ref=sr_1_3?s=books&ie=UTF8&qid=1525704787&sr=1-3&keywords=power+pivot+and+power+bi

and have a couple more which I might check out (This one by Phil Seamark).

And also watch a LOT  of videos,  tutorials and blogs such as:

Microsoft

Enterprise DNA

SQLBI

Exceleratorbi

Guy in a cube

and many others...

I have found that it is really important to dig into a "real" project.

Hope you enjoy the journey!

Regards,

Paul.

[PaulDBrown · Accepted solution]

@nxsjd

Interesting, because that adds a new context, and that entails re-doing the measures. 

This seems to work:

For the cumulative 'real' and 'target' measures, you need to wrap the table ref. with the ALLSELECTED (as per your orignal measure).

Cumulative real = CALCULATE(SUM('Table'[Real]);
    FILTER(ALLSELECTED('Table');
        'Table'[Month Order]<= MAX('Table'[Month Order])
    )
)

Cumulative target = CALCULATE(SUM('Table'[Target]);
    FILTER(ALLSELECTED('Table');
        'Table'[Month Order]<= MAX('Table'[Month Order])
    )
)

And for the dynamic cumulative target line, you need:

Dynamic Target Ref Line = 
VAR Selectedmonth  = SELECTEDVALUE(Months[Month Order])

RETURN
CALCULATE([Cumulative Target]; 
    FILTER(ALLSELECTED('Table');
        'Table'[Month Order] <= Selectedmonth)
)

Here is the PBIX file

Try it and see if it works.

Regards,

Paul.

[nxsjd]

@PaulDBrown

This works well! Thank you so much Paul!