Forum Discussion

ajazz's avatar
ajazz
Frequent Visitor
5 years ago
Solved

Dynamic Reference Line on line chart

Hi there, I was wondering if it is possible to add a dynamic reference line to a line chart. In my line chart I have some filtered products by a slicer. On the y-axis i have price and x-axis i ...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    4 years ago

    Hi  ajazz ,

     

    Sorry for the late reply.

    Create 2 measures as below：

    Average =
AVERAGEX (
    FILTER (
        ALLSELECTED ( MedicinPriserUnpivot ),
        'MedicinPriserUnpivot'[Varenummer] = MAX ( MedicinPriserUnpivot[Varenummer] )
            && 'MedicinPriserUnpivot'[Dato] = MAX ( 'MedicinPriserUnpivot'[Dato] )
    ),
    'MedicinPriserUnpivot'[Pris]
)

    Measure2 =
VAR _tab =
    CALCULATETABLE (
        VALUES ( 'Vareoversigt'[Varenummer] ),
        FILTER (
            ALL ( Vareoversigt ),
            'Vareoversigt'[Substitutionsgruppe]
                IN FILTERS ( Vareoversigt[Substitutionsgruppe] )
        )
    )
RETURN
    CALCULATE (
        MINX ( ALLSELECTED ( MedicinPriserUnpivot ), [Average] ),
        FILTER (
            ALLSELECTED ( 'MedicinPriserUnpivot' ),
            'MedicinPriserUnpivot'[Dato] = MAX ( 'MedicinPriserUnpivot'[Dato] )
                && 'MedicinPriserUnpivot'[Varenummer] IN _tab
        )
    ) + 50

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my reply as a solution!

v-kelly-msft's avatar
v-kelly-msft
Community Support
5 years ago

Hi ajazz ,

 

Do you mean that the reference line is combined by loweast price of each month +50?

 

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

ajazz's avatar
ajazz
Frequent Visitor
5 years ago

It needs to be for each date point, so not month. Like lowest price productnumber from monday 15 july 2019 + 50.

 

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    5 years ago

    Hi  ajazz ,

     

    Measure is easy to create,but line chart doesnt support measure as secondary values,what I can think is to put measure as a tooltip,such as below:

    Or change the visual to "line and stacked column chart instead".

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my reply as a solution!

    • ajazz's avatar
      ajazz
      Frequent Visitor
      5 years ago

      Great job!

      Is it possible to have multiple lines in a "line and stacked column chart"? If so that could be a good solution.

      How does your function for measure look like?

    • ajazz's avatar
      ajazz
      Frequent Visitor
      5 years ago

      Hi Kelly,

      I just saw that you have attached a copy. Thanks!

      I have a question:

      I can see it filters values by date. Is it possible to filter the measure by the "Sliced" values in a slicer?

       




      • v-kelly-msft's avatar
        v-kelly-msft
        Community Support
        5 years ago

        Hi ajazz ,

         

        Change "ALL" to "ALLSELECTED " should work,check below:

        Measure = CALCULATE(MIN('Table'[Price]),FILTER(ALLSELECTED('Table'),'Table'[Date]=MAX('Table'[Date])))+50

         

        Best Regards,
        Kelly

        Did I answer your question? Mark my reply as a solution!