Forum Discussion
Show vertical bar chart without grouping items
I have data that spread across columns that I want to show in a vertical bar chart. The problem is the bar chart groups everything together because there really is no x-axis component to define.
| Year 2021 | Year 2022 | Year 2023 | Year 2023 | Year 2025 |
| 213212 | 6345 | 23432 | 32423 | 23423 |
| 213232 | 3432 | 42332 | 43243 | 3243242 |
| 312115 | 32432 | 43232 | 23432 | 32432 |
| 353211 | 342332 | 32432 | 324324 | 343242 |
I want to show the sum of each year on a vertical bar graph with a target for each.
Problem is it get grouped together because I am unable to define a x-axis.
I know unpivot is a possible option but it doesn't work in my case because the actual data has lots of columns and is much more complicated than the sample table shown above.
What I end up now is something like this:
Any suggestion are higly appreciated!
Hi Anonymous ,
The easiest way to do this is to go to the Query Editor, select all columns, and then select Unpivot Columns.
However, you can also perform this operation in DAX, you can perform the following formula
1. Create calculated table.
Table 2 = UNION( SELECTCOLUMNS( 'Table', "Year",2021, "Value",[Year 2021]), SELECTCOLUMNS( 'Table', "Year",2022, "Value",[Year 2022]), SELECTCOLUMNS( 'Table', "Year",2023, "Value",[Year 2023]), SELECTCOLUMNS( 'Table', "Year",2024, "Value",[Year 2024]), SELECTCOLUMNS( 'Table', "Year",2025, "Value",[Year 2025]))2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
5 Replies
Hi Anonymous ,
The easiest way to do this is to go to the Query Editor, select all columns, and then select Unpivot Columns.
However, you can also perform this operation in DAX, you can perform the following formula
1. Create calculated table.
Table 2 = UNION( SELECTCOLUMNS( 'Table', "Year",2021, "Value",[Year 2021]), SELECTCOLUMNS( 'Table', "Year",2022, "Value",[Year 2022]), SELECTCOLUMNS( 'Table', "Year",2023, "Value",[Year 2023]), SELECTCOLUMNS( 'Table', "Year",2024, "Value",[Year 2024]), SELECTCOLUMNS( 'Table', "Year",2025, "Value",[Year 2025]))2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thanks for the reply, I know that unpivot can work with sample data given but the real data I am dealing with is not a straightforward colum but rather a Calculated field
2023 Calc =
CALCULATE(
[2023],
'Inventory'[Direct vs Indirect] IN { "Direct" },
'Inventory'[Benefit Type] IN { "Costs" },
LASTDATE('Calendar'[Date])
)When I plot it right now using the clustered vertical I get this for year 1 and year 2. The numbers are correct, it's just that they are grouped together that's the issue
When I use your suggestion :
Table 2 =
UNION(
SELECTCOLUMNS(
'Inventory',
"Year",2023,
"Value",[2023 Calc]),
SELECTCOLUMNS(
'Inventory',
"Year",2024,
"Value",[Year 2024])
))I end up getting
Not sure where those numbers are coming from, is it just not respecting the filters in the calculated measure?
Hi Anonymous ,
If I understand your question, you are looking for something like this?Create a measure for SUM () of each year.
YR 2021 = SUM(TableT[Year 2021])
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
