Forum Discussion

nxsjd's avatar
nxsjd
Frequent Visitor
8 years ago
Solved

Create a Dynamic Reference Line Based on A Cumulative Measure

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

Need Help
  • PaulDBrown's avatar
    PaulDBrown
    8 years ago

    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's avatar
    PaulDBrown
    8 years ago

    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.

7 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    8 years ago

    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's avatar
      nxsjd
      Frequent Visitor
      8 years ago

      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's avatar
        PaulDBrown
        Community Champion
        8 years ago

        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.