cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ts_snoco
New Member

How to Make a Simple Bar and Line Chart

Hello, 

 

I am attempting to create the following chart:

 

ts_snoco_0-1674669552207.png

 

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

ts_snoco_1-1674670762576.png

 

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?

2 REPLIES 2
AllisonKennedy
Super User
Super User

@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 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

@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. 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.