Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 :
Solved! Go to 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.
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.
Hi,
In a simple Table, please show the expected result. Once we get the result in a Table, we can build any visual.
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.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |