Dynamic Reference Line on line chart

[ajazz]

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 have date.

My objective is to show a dynamic reference line, where it takes the cheapest filtered product and adds the value 50 to the price. It should be dynamic, so that it takes the cheapest product from that particular date and not the general cheapest product.

I would like to show the dynamic reference line on the line chart.

I have tried to draw how it should be (the red line is the dynamic reference line that im seeking):

Any help would be very appreciated.

Thanks in advance!

[v-kelly-msft]

Hi @ajazz ,

I see your data,how to caculate the reference line?

I took part of your data as below:

Could you pls advise me how to get the reference line?What is the calculation logic?

Best Regards,
Kelly

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

[ajazz]

Hi again,

It needs to be average price per ProductNumber.
Then a slicer to select 3-4 productnumbers.

Of the "filtered" view, the reference line needs to take the ProductNumber with the lowest price and add +50 to it.

Does that make sense?

[v-kelly-msft]

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]

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

[v-kelly-msft]

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]

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]

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!

[ajazz]

Hi @v-kelly-msft,

I appreciate you efforts a lot!

We are getting very close, but it seems like its not calculating correct?

Thanks again!

[v-kelly-msft]

Hi @ajazz ,

I cant reproduce your senario,would you pls share your .pbix file for test?

Remember to remove the confidential information.

Best Regards,
Kelly

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

[ajazz]

Hi @v-kelly-msft 

I have attached a link to the pbix here with the used datasets.
https://we.tl/t-SuEKSu1Y25

I would recommend you to slice with value 58 in 'Substitutionsgruppe'

Huge thanks!!

[v-kelly-msft · Accepted solution]

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!

[ajazz]

Awesome @v-kelly-msft 

You helped me a bunch! Thanks a lot!

[v-kelly-msft]

Hi  @ajazz ,

Glad to help!🙂

Best Regards,
Kelly

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

[ajazz]

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?

[v-kelly-msft]

Hi @ajazz ,

Could you pls provide some sample data with expected output for test?

Remember to remove the confidential information.

Best Regards,
Kelly

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

[ajazz]

Hi,

I have attached a sample data set here:
https://we.tl/t-RNSBlkM5Vf

THANKS!