Hi all,
I have the task to create a bar chart that displays the budget per year that got spent for two products and the budget that was available for those projects as well as the over all budget of that year.
I am currently having issues to come up if the different functions for the bars, groups and the height of the bars.
I would like to use the year as the group which is easy but I haven't found a solution on how to define the different bars. In the following you will find some exemplary data, the requirements for the individual bars (not the function that I need to insert to build the visualization) and attached also the end result I would like to achieve.
| Product |
Budget_for |
Already_spend |
Budget |
| A |
2023 |
Yes |
10 |
| B |
2023 |
Yes |
20 |
| A |
2023 |
No |
10 |
| B |
2023 |
No |
20 |
| A |
2024 |
Yes |
10 |
| B |
2024 |
Yes |
20 |
| A |
2024 |
No |
10 |
| B |
2024 |
No |
20 |
| |
2023 |
No |
30 |
| |
2024 |
No |
30 |
Here the different requirements for the bars derived from the table above:
Spent Budget A 2023 = SUM(IF(Product='A' and Budget_for='2023' and Already_spend='Yes', Budget,0))
Spent Budget B 2023 = SUM(IF(Product='B' and Budget_for='2023' and Already_spend='Yes', Budget,0))
Available Budget A 2023 = SUM(IF(Product='A' and Budget_for='2023', Budget,0))
Available Budget B 2023 = SUM(IF(Product='B' and Budget_for='2023', Budget,0))
Budget 2023 = SUM(IF(Budget_for='2023'))
Spent Budget A 2024 = SUM(IF(Product='A' and Budget_for='2024' and Already_spend='Yes', Budget,0))
Spent Budget B 2024 = SUM(IF(Product='B' and Budget_for='2024' and Already_spend='Yes', Budget,0))
Available Budget A 2024 = SUM(IF(Product='A' and Budget_for='2024', Budget,0))
Available Budget B 2024 = SUM(IF(Product='B' and Budget_for='2024', Budget,0))
Budget 2024 = SUM(IF(Budget_for='2024'))
