Forum Discussion
Creating Comparison Bar Chart - Multiple Categories (using measures)
I need to create a graph that shows comparison of several categories between 2 periods (Q1 and Q2 2023)
So I created measures to calculate averages of columns in Table 1 (Q1 2023) and Table 2 (Q2 2023)
Example:
Table 1
A Q1 2023 average measure
B Q1 2023 average measure
...
Table 1
A Q2 2023 average measure
B Q2 2023 average measure
...
On a simple table, it will look like this:
| Category | 2023 Q1 | 2023 Q2 |
| A | 8.69 | 7.69 |
| B | 8.57 | 7.57 |
| C | 8.13 | 7.13 |
| D | 8.34 | 7.34 |
| E | 8.4 | 7.4 |
| F | 8.7 | 7.7 |
| G | 8.79 | 7.79 |
| H | 8.57 | 7.57 |
| I | 8.77 | 7.77 |
| J | 8.44 | 7.44 |
| K | 9.03 | 8.03 |
| L | 8.7 | 7.7 |
| M | 8.4 | 7.4 |
| N | 8.51 | 7.51 |
| O | 8.83 | 7.83 |
| P | 8.83 | 7.83 |
Now, I want to create one graph that will look like this using the measures, but it's just lumping in all the bars:
I cannot think of how to put it this way. Should I create a separate table that calls the categories and their values (and how)? I don't know how to begin doing it.
2 Replies
Hi Anonymous ,
According to your description, I create a sample to reproduce the problem.
Table1:
Table2:
I create two measures:
2023 Q1 = AVERAGE(Table1[Q1 2023])2023 Q2 = AVERAGE(Table2[Q2 2023])In a clustered column chart, put Category in X-axis and the two measures in Y-axis, get the result:
Is this your expected result? I attach my sample below for your reference.
Best regards,
Community Support Team_yanjiang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Actually, the Categories A-Z are actually columns in each of my two tables, with 1-10 values.
For example:
Table 1 Country Location A B C D E F G H US New York 8 6 5 5 8 8 6 5 Philippines Manila 7 4 6 4 7 7 4 6 Philippines Manila 6 3 7 6 5 6 3 7 US California 4 4 5 7 4 4 4 5 UK London 5 8 2 3 3 5 8 2 Africa Johannesburg 7 8 10 8 5 7 8 10 US New York 8 9 2 9 8 8 9 2 UK Ireland 9 6 8 7 9 9 6 8 Australia Sydney 10 7 4 6 2 10 7 4 Table 2 Country Location A B C D E F G H Latin America Brazil 8 7 6 4 5 7 8 9 Latin America Mexico 6 4 3 4 8 8 9 6 Australia Melbourne 5 6 7 5 2 10 2 8 New Zealand New Zealand 5 4 6 7 3 8 9 7 EMEA Greece 8 7 5 4 3 5 8 9 EMEA France 8 7 6 4 5 7 8 9 US New York 6 4 3 4 8 8 9 6 EMEA Greece 5 6 7 5 2 10 2 8 Australia Sydney 10 7 4 6 2 10 7 4 The measures I created were Q1 A, Q1 B, etc etc. then Q2 A, Q2 B. But when I'm creating a graph, it doesnt show that way. They are all lumped together.
The measures I created, if I put it in excel, will look like this:
