Show vertical bar chart without grouping items

[zb134]

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!

[v-yangliu-msft · Accepted solution]

Hi  @zb134 ,

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

[zb134]

I had to do lots of transformations to get it right, but ultimately your directions helped a lot in getting it done!

[zb134]

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?

[Nathaniel_C]

Hi @zb134 ,
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

[zb134]

Thanks for the reply but I need it not to be grouped together as shown but seperated under x-axis categories for each year.