Add extra measure to line chart with legend

[FotFly]

Hi all,

I have a line chart in which I have added dates on the X axis, a measure that counts product sales per date (the product sales and the calendar table are strategically unconnected) in the Y-axis and the product name as Legend to create a line for each product. Everything works fine, until I want to present the average sales per product (out of all products and not just the average of the selected). So for this purpose I have created a measure that removes the filter of the product name and returns the actual average. The thing is that I cannot add that measure to my chart. I have tried further analysis but it does not allow me to add my measure, it only has some presets.

The other idea I had was to create two overlapping tables on top of each other that I have aligned on the values. This way I can have both visuals. The problem starts when there is a product for which on a specific time period there are no sales. Then some periods disappear from the front tablet and the two charts do not agree. I still want the user to be able to determine the dates that appear on the visuals while keeping them aligned.

[Greg_Deckler]

Another thought on this @FotFly, could you use the Analysis pane and use the Y-Axis constant line and use your measure there for the average?

[Kedar_Pande]

Create a Disconnected Legend Table:

DisconnectedProducts = DISTINCT('Sales'[ProductName])

Measure:

Total Sales = COUNTROWS('Sales') -- Replace with your existing sales logic

Measure:

Average Sales = 
CALCULATE(
AVERAGEX(ALL('Sales'[ProductName]), [Total Sales])
)

Ensure your X-axis dates are continuous. In your line chart’s X-axis settings:

Set Type to Continuous.
Ensure the Calendar or Date table includes all dates, even those where no sales exist for certain products.

Now, add your Total Sales measure to the Y-axis, and the Average Sales measure will be a line that shows the overall average, regardless of the product filter. Add:

Date to
the X-axis.

Product Name (from the disconnected table) to the Legend.
Total Sales (your existing sales measure) to the Values.

To display the Average Sales on the same chart, add it as a constant line:
Go to the Analytics pane of the chart.
Add a Line under the Constant Line section.
Set the value to your Average Sales measure. This will display a horizontal line across all products and dates, representing the average sales across all products.

If this helped, a Kudos 👍 or Solution mark would be great!🎉
Cheers,
Kedar Pande
Connect on LinkedIn

[FotFly]

Thank you for your answer. As I stated in my original post I tried Analysis and the constant line but even adding my measure as a value returned back a horizontal constant line. My average is by no means constant.

The only solution that I came up with for my second idea was to has another calendar table for which the user will define the time period and then by collecting the dates on that second table using VALUES() I am able to write my measure for getting the sum of sales (broken down by the product in the legend) and my average measure and forcing both measures to return 0 if there is no value for each date in my calendar that is in the selected list.

This really does solve the issue of having to align the two overlapping charts, however it worsens the performance of the visual. The visual takes a lot of time to load with all these calculations.

[Greg_Deckler]

@FotFly Perhaps try creating a disconnected table of product names with an additional row for your average. Use this for your legend and then write a single measure that returns either the count of product sales per date (for each category) or the average. 

Sharing sample data would help tremendously to be able to mock this up and test various solution strategies. 

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[FotFly]

This is a good approach however I am concerned with having to duplicate all the tables for which I have a visual similar to the one of the products and sales.