Forum Discussion
Relationships between tables
Hello!
I am currently working with 3 tables.
Budget: Containing the Monthly Budget of each Store
Ventas_Full: Contains the transactions of each store
Date: Date table
What I need to show in a table is the accumulated daily budget ( Which I get by calculating: Monthly Budget / Days of the Month * Current day, for example: $10,000 / 31 days in January * 11 days )
What is the problem: Budgets and Sales are related by a key column, which contains The Brand-Store-Month, currently the relationship as shown in the image works without problems, except for one detail, if a STORE does not contain sales to date, when putting a budget table, it ignores the store that does not report sales (there is no data) and gives me a lower value than I want.
Example:
| Shop | Sales | Budget |
Shop 1 | $10.000 | $8.000 |
| Shop 2 | $15.000 | $10.000 |
| Shop 3 | - | - |
| Total | $25.000 | $18.000 |
What do I need:
| Shop | Sales | Budget |
Shop 1 | $10.000 | $8.000 |
| Shop 2 | $15.000 | $10.000 |
| Shop 3 | - | $5.000 |
| Total | $25.000 | $23.000 |
That is, although the key does not find any match in the sales table, it shows me that budget in the same way.
Any idea of how I could do it?
Thanks from Ante Mano.
2 Replies
Syndicate_Admin , Refer if my blog on same topic can help you
Distributing/Allocating the Monthly Target(Convert to Daily Target): Measure ( Daily/MTD): https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Monthly-Target-Convert-to/ba-p/1657798
Hi, Syndicate_Admin
Can you provide the simple data you need to use for each of the 3 tables? Especially the relevant columns between the tables and the tables. Looking forward to your reply.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
