Forum Discussion
I need help to create a new table from another one
I don't know if this is tricky or is just me, but here goes nothing.
I have one table with a group of columns that are months and another group of columns that are also months, each group have a different context in its values; one group is a planified budget, the other group is the real disbursement. Something like this:
| id | title | jan_planified_amount | feb_planified_amount | (…) | jan_real_amount | feb_real_amount | (...) |
| 1 | example_1 | $500,00 | $0,00 | (…) | $0,00 | $500,00 | (…) |
So I tried creating two new tables, one for each group and unpivot the columns, keeping the ids, the months and the amount. Then I create a relation many to many and create a new table with only ids, but when I tried putting the values in a chart it does not work.
So I need another way to relate these tables or create only one table with both values like this:
| id | title | month | planified amount | real amount |
| 1 | example_1 | jan | $500,00 | $0,00 |
| 1 | example_1 | feb | $0,00 | $500,00 |
| 1 | example_1 | mar | $1.000,00 | $1.000,00 |
| 1 | example_1 | apr | $0,00 | $0,00 |
| 1 | example_1 | may | $0,00 | $0,00 |
| 1 | example_1 | jun | $0,00 | $0,00 |
| 1 | example_1 | jul | $0,00 | $0,00 |
| 1 | example_1 | aug | $0,00 | $0,00 |
| 1 | example_1 | sep | $1.000,00 | $0,00 |
| 1 | example_1 | oct | $0,00 | $0,00 |
| 1 | example_1 | nov | $0,00 | $0,00 |
| 1 | example_1 | dic | $1.000,00 | $2.000,00 |
Because I need a chart like this:
Been the bars the planified amount and the line the real amount.
I don't know if this is the best way to achieve this but is the only thing I cant think of. I need a little help.
Hi ScripTeapot_ , budgeting in PowerBI is possible but you should be aware of details of your model. Example, in your budget there could be product which there are not in actuals ... Many to many relationship could create ambigious results so be careful.
If not already, please check link by SQLBI for this topic
https://www.sqlbi.com/tv/budgeting-with-power-bi/ and related pattern (paid). I did not use paid but I used other content and it is great https://www.sqlbi.com/p/dax-patterns-budget/
Personally, this is not easy to implement as requires a lot of modeling.
2 Replies
- some_bih
Community Champion
Hi ScripTeapot_ , budgeting in PowerBI is possible but you should be aware of details of your model. Example, in your budget there could be product which there are not in actuals ... Many to many relationship could create ambigious results so be careful.
If not already, please check link by SQLBI for this topic
https://www.sqlbi.com/tv/budgeting-with-power-bi/ and related pattern (paid). I did not use paid but I used other content and it is great https://www.sqlbi.com/p/dax-patterns-budget/
Personally, this is not easy to implement as requires a lot of modeling.
thank you for all the help! I will study this.
