Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
Hi @kay87,
I have made a mockup of a file using only dates + value and calculating the moving average:
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.
As you can see the average is moving with the changes of the slicer.
Not sure if this is waht you need.
Regards,
Mfelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank 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
Hi @kay87,
I have made a mockup of a file using only dates + value and calculating the moving average:
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.
As you can see the average is moving with the changes of the slicer.
Not sure if this is waht you need.
Regards,
Mfelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank 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
Hi @kay87,
See attach the PBIX file.
Be aware it's a we transfer link so will only last 7 days.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsDidn'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.
User | Count |
---|---|
91 | |
73 | |
68 | |
63 | |
55 |
User | Count |
---|---|
99 | |
88 | |
73 | |
60 | |
58 |