Line chart - Combine measure and average ?

[clem312]

Hello,

I have a table as below :

Year	Brand	Notation
2019	Brand 1	10
2015	Brand 1	9
2019	Brand 1	8
2019	Brand 2	10
2016	Brand 2	9
2019	Brand 2	7
2019	Brand 2	8
2019	Brand 3	8
2015	Brand 3	9
2019	Brand 3	10
2019	Brand 3	7
2016	Brand 1	4
2017	Brand 1	7
2018	Brand 1	6
2019	Brand 3	1

I need to make a line chart with year on X and Average on Y.

One line for each brand.

That I can do. What I can't do, is to add a line "Average of all brands" for each year.

I can't find out how.

Like this :

[v-stephen-msft]

Hi @clem312 ,

You can create a table like

There's a relationship between the new table and the main table.

Measure = IF(MAX('Table (2)'[Brand])="Average of all brands",CALCULATE(AVERAGE('Table'[Notation]),ALLEXCEPT('Table','Table'[Year])),SUM('Table'[Notation]))

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

In a simple Table, please show the expected result.  Once we get the result in a Table, we can build any visual.

[lbendlin]

It's not that simple. You have multiple values per brand and year, so you will need to define what you mean by "Average".  Below example uses ALLEXCEPT and calculates the average of all values per year. This may or may not be what you intended.

Avg = CALCULATE(AVERAGE('Avg'[Notation]),ALLEXCEPT('Avg','Avg'[Year]))

If you need a line chart for all values then you can either merge tables (if calculated columns are ok) or you need to create separate measures for the brands plus one for the average.

[clem312]

Hi @lbendlin,

Thank you for your answer.

The average requested is the sum of average of each brand average divided by the number of brand. So that we have an average of the industry for each year.

Best regards,

Clement

[v-stephen-msft · Accepted solution]

Hi @clem312 ,

Sorry for my negligence. Please check my new attachment.

1.I create a new seperate table like

Table 2 = CROSSJOIN( UNION( VALUES('Table'[Brand]), {"Average"}), VALUES('Table'[Year]))

2.Then create the measure

Measure = IF(MAX('Table 2'[Brand])="Average",  CALCULATE(SUM('Table'[Notation]) / DISTINCTCOUNT('Table'[Brand]),FILTER('Table','Table'[Year]=MAX('Table 2'[Year]))) , CALCULATE(SUM('Table'[Notation]),FILTER('Table','Table'[Brand]=MAX('Table 2'[Brand]) && 'Table'[Year]=MAX('Table 2'[Year])  )))

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.