Latest Enterprise DNA Initiatives

Budget vs Actual calculations

I have two tables.

  1. Budget File ( Columns- Material Requisition, Proposal Budget)
  2. Actual Details

I want create table which will help to compare Budget , Budget Released ( Purchase Requisition) & PO amount (Purchase order value).

I have attached expected output in excel file ( File Name- OUTPUT.XLSX).

Thanks in advance (68.8 KB)

Bumping this post for more visibility.

Here’s how I would approach this scenario. The key is to put the Budgets and Actuals into a single table. I did that using Power Query. To do that, I created staging tables for Budgets and Actuals with the same six columns:

Material Requistion No
Budget Amount
Purchase Requisition No
Budget Released Amount
PO Amount

You can see in the Query Editor that I had to populate blank for actuals fields on my budget staging tables and vice versa. Then, I appended the two together into a single fact table.

Everything else was pretty much as you did in your output file. I built the same matrix visual with the same basic key measures. The only change is to the two Savings measures. I checked if it is a total row by using the HASONEVALUE function. That really cleans up the presentation of those columns to me.

Output File MH2.pbix (30.2 KB)

Thank you for your help.

can we exclude proposal budget line from purchase requisition column ?

Yes. Here it is with that change.

Output File MH2.pbix (29.5 KB)

Dear Sir,

Thank you for your help & time.

But I am expecting output same as in attached image.

We are limited to formatting options available to us in the matrix visual in PowerBI. But, we can get it closer to looking like your Excel file. These formatting options seem to be the closest. But, you may want to play around with the subtotals options to see if there is something that you like better.

I put the subtotals on top because that is how your Excel file does section by material req no. But, then the grand totals also show on top.

Output File MH2.pbix (29.7 KB)

Hi @prafullchavan, did the response provided by @mhough help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

It helped me to achieve similar output.

But I am expecting result as mentioned in my output excel file.

Bumping this post for more visibility.