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 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?
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
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
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.
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
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 |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |