Forum Discussion
visual with incorrect total
Hi All,
I have a query with daily actual sales by region and a separate query with monthly budget by region that i divide by the working days to obtain a theoretical daily budget. I
When I use a stack column chart, the actual sales are returned nicely for the day in the filter (i.e. today) by region. The budget sales however are summed up to the total (all regions) daily budget.
I cannot get my head around this one, can you please share any thoughts as to how to obtain the daily budget sales by territory? Do I have to merge the two queries perhaps?
a screenshot of the filters and axis, values visualizations:
Many thanks,
6 Replies
Anonymous usually it is a relationship issue. How the budget table is connected with the table you are using for the date in the visualization. if the relationship is not correct or doesn't exist, it will not show the correct value because the table is not getting filtered.
Share the relationship diagram to further look into the issue.
✨ Follow us on LinkedIn
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
parry2k thanks for getting back so quick
The actual sales query contains all of the data (value, date, region) from the invoicing system.
The budget query is a simple excel table, containing Month and Daily Sales (the result of dividing Month Sales / working days). Are you suggesting It should contain today's date instead?
Thank you,
Anonymous How your tables are connected and what is the granularity, it is critical. You didn't even reply to the question I asked 🙂
Hi parry2k
this is the query dependencies:
The act sales consol query is more detailed as it contains daily sales by product and customer which I am not using in the stacked columns chart.
The budget sales query is only territory, budget month and budget day sales.
I hope this is sufficient info. If you require additional info please do let me know.
Thank you,
hi Anonymous
The relatiosnhip between the two queries is the Invoice date and the territory. Due to the fact budget figures are monthly I have added a column with days where the monthly sales are divided by the working days of the month. So for example for North America there are 23 days for December with same daily budgeted sales amount which is then compared to the actual daily sales query.
Obviously I am doing something wrong, as the budget sales in the stacked columns chart add up to the month total.
I hope I answered your question as expected. Please do let me know if you have any questions.
Best Regards,
