Reference Line Question

[kay87]

Hi All

I am an advanced Qlik user and looking to find a way to add a reference line to a trend chart in Power BI.

I have a calculated measure ie KPI_5_BaseLineMean which returns one value depending on date selections. I would like to add this measure as a reference line to the line chart. Please see the example screenshots below.

Regards

Kay

[MFelix · Accepted solution]

Hi @kay87,

I have made a mockup of a file using only dates + value and calculating the moving average:

• Create a date table
• Make a non-active relationship between both table based on date
• Create the following measure:

• BASE_LINE = 
  CALCULATE (
      AVERAGEX (
          SUMMARIZE (
              ALL ( 'Fact Table'[Date]; 'Fact Table'[Values] );
              'Fact Table'[Date];
              "AVERAGE VALUES"; AVERAGE ( 'Fact Table'[Values] )
          );
          [AVERAGE VALUES]
      );
      'Fact Table'[Date] = VALUES ( 'Fact Table'[Date] );
      USERELATIONSHIP ( DimDate[Date]; 'Fact Table'[Date] )
  )

  Be aware that I'm using AVERAGEX but based on your context you may have to use another type of aggregator.
• Create a slicer with the Date table date
• Add the measure to your line chart along side with the values.

As you can see the average is moving with the changes of the slicer.

Not sure if this is waht you need.

Regards,

Mfelix

[MFelix · Accepted solution]

Hi @kay87,

I have made a mockup of a file using only dates + value and calculating the moving average:

• Create a date table
• Make a non-active relationship between both table based on date
• Create the following measure:

• BASE_LINE = 
  CALCULATE (
      AVERAGEX (
          SUMMARIZE (
              ALL ( 'Fact Table'[Date]; 'Fact Table'[Values] );
              'Fact Table'[Date];
              "AVERAGE VALUES"; AVERAGE ( 'Fact Table'[Values] )
          );
          [AVERAGE VALUES]
      );
      'Fact Table'[Date] = VALUES ( 'Fact Table'[Date] );
      USERELATIONSHIP ( DimDate[Date]; 'Fact Table'[Date] )
  )

  Be aware that I'm using AVERAGEX but based on your context you may have to use another type of aggregator.
• Create a slicer with the Date table date
• Add the measure to your line chart along side with the values.

As you can see the average is moving with the changes of the slicer.

Not sure if this is waht you need.

Regards,

Mfelix

[kay87 · Accepted solution]

Thank you soo very much for this MFelix. This is very close to what I want to do. My average formula is very different but this is massively helpful and point sme in a direction. 

Is there a chance you could share the bix file for my reference?

Regards

Kay

[MFelix]

Hi @kay87,

See attach the PBIX file.

Be aware it's a we transfer link so will only last 7 days.

Regards,

MFelix

[Greg_Deckler]

Didn't get the screen shots. You could potentially do this via something like a clustered column and line chart visual.

OK, now I see the screen shots. Weird. Well, currently you cannot use columns or measures in Analytics pane constant lines to the best of my knowledge.