Need Help with showing correct figures in Sales amount KPI and Budget amount KPI

[dushyant]

Hi,

I have a sales table with products sold on different dates and a budget table with budget amounts (month wise) for all the products for 12 months (Apr'24-Mar'25).

I have made an association between both the tables by creating a composite key using product_code, monthyear and Region fields.

In front-end, when I click on ytd button, the sales amount is showing correctly while the Budget is only reflecting of those products of which sale has happened in sales table. But I want to also include budget of those products for which sale has not happened.

[Qrishna]

pls provide some sample data.

[rubenmarin]

Hi, if year is only linked to the sales table, and there is no sales of some product for that year, there will be no association between the year and the budget table for that products.

To avoid this there are at least 2 options:

1. Add all the budget composite keys to the sales table, so there is relation with the calendar table or the year field for all combinations that exists on the dugets table.

2. Concatenate Sales and Budget in the same table, in this case maybe you will need a field that identifies the type of data and use this on set analysis of each expression; or just have a differnt field name for real sales than budgeted sales.

[barnabyd]

G'day @dushyant , I use a variant of @rubenmarin 's solution:

Concatenate the Sales and Budgets into the same table, but keep the Sales Amount and the Budget Amount in separate columns. With this data model, you can:

1. select a date and it will filter both Sales and Budgets correctly
2. create measures like sum( Sales ) and sum( Budget ) and they both work without any flags

I hope this helps. Cheers, Barnaby.