Actual vs Budget - amounts not pulling through in a matrix when there is no budget

[pbi_newie]

Hello,

I have a direct connection to a data source where I have built an Actual vs Budget comparison and the issue I have is that when there is no budget against a particular cost center, the value for Actuals drops off the table - see below where the table only shows Actual vs Budget for October but drops off Actuals for November and December:

When I compare Actual vs Actual, you will see that the values for November and December Actuals come through when they were missing against the above comparison vs Budget as there was no budget for November and December for this cost center yet there was actual spend in those months.

I have tried things like replacing blanks in the Budget scenario with zeroes or even a value of 1 for example and that doesn't seem to work.

Any help would be highly appreciated.

[pbi_newie]

@v-yaningy-msft I've managed to find out how the data is stored in the server and it looks like there is no value for the Budget scenario for the month of Decmber 2023. When comparing Actual vs Budget using a slicer to select the Budget scenario, PowerBI drops the Actuals for the month of December 2023 as there is no budget comparative.

If I was to share a view of how the data looks like, it would be as follows:

Cost Center	Month	Scenario	Amount
12345	2023-10	Actual	-104.00
12345	2023-10	Budget	122,852.00
12345	2023-10	Forecast	122,852.00
12345	2023-11	Actual	7,009.00
12345	2023-11	Forecast	10,000.00
12345	2023-12	Actual	101,315.00

Based on the above dataset, PowerBI only shows Actual vs Budget for the month of October which is the only month which has values against both the Actual and Budget scenarios. The rest of the months are being dropped off from the matrix visual.

Hope this provides some additional context.

[v-yaningy-msft]

Hi, @pbi_newie 

You can try using the budget column directly in the visual object instead of using the metric. This ensures that your actual values are compared to the "budget" that is always present (even if it is artificially set to zero), and that the actual values are displayed even if no budget is specified.
If you can provide a table or pbix file without sensitive data for testing, it might solve your problem faster.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[pbi_newie]

Hi @v-yaningy-msft 

Thank you for your response.

So with the budget column directly in the visual it works however then you have the issue where the visual is meant to be dynamic i.e. sometimes you might want to compare actuals vs a forecast scanario, etc.

Essentially how the data is structured is you have an amount column and then a scenario column which has scenarios such as Actual, Budget, Forecast etc.

As the Actuals column is constant, i have the following mesaure for it:

Actual = CALCULATE(SYSTEM[Amounts],SYSTEM[Scenario]="Actuals")

Currently, you I have the scenario column as dynamic i.e. I have a slicer with the following measure:

Selected_Scenario = SYSTEM[Amounts]

I beleive it is the above measure where I may need to make some tweaks?

Unfortunately i am unable to upload the PBIX file due to internal company restrictions. The PBIX file is also directly connected to the system (via a SSAC connection) so i don't have access to the dataset in table format.

Let me know if the above helps or if there is anything adidtional i can provide to help assess the issue.

Thanks for your assistance so far!