How to Make a Simple Bar and Line Chart

[ts_snoco]

Hello, 

I am attempting to create the following chart:

I have multiple years of data and it is formatted as follows:

I have 14 unique values for 'Site Name'. 'Year' entries range from 1988-2022. How do I achieve the desired chart without reformatting my data?

[AllisonKennedy]

@ts_snoco 

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]

@ts_snoco  

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.