Forum Discussion
How to Make a Simple Bar and Line Chart
Community Champion
You don't need to reformat the raw data, but do need to transform it a bit in Power Query. Select all the Month columns, then click "Unpivot" > "Only Selected Columns". (If you need more details on how to get into the transform in Power Query let me know).
This will give you an 'attribute' and 'value' column. You can use the 'Attribute' in the 'x-axis' and put the 'value' in the y-axis multiple times, changing the summarization to max, min, average. For the 2022 line you'll need to create an explicit DAX measure:
2022 =
SUMX(
FILTER(table,
table[Year] = 2022
),
table[value]
)
If you copy that measure into Power BI desktop, you can select the word 'table' and use Ctrl Shift L to highlight all the instances of 'table' and then replace with your own table name. https://excelwithallison.blogspot.com/2023/01/copy-and-paste-dax-code-power-bi-hack.html
- AllisonKennedy
Another tip, you might like to try using a box and whiskers visual for this data, as it's designed to show the max, min, average, and median values for you automatically. You can find these by clicking the three dots in the visualization pane and adding visuals from marketplace.