Buget vs Actual on one single table

Question

I have the following transactions table:

CCost	Account	Date	Budget	Actual	Description
1	1	20190101	100	10	Gas
1	1	20190102	100	50	Salary
1	1	20190201	200	50	Gas
1	1	20190202	200	150	Salary
1	1	20190203	200	10	Gas

CCenter is the Cost Center, Account is the bank account, date is the date of the transaction, budget is the budget for that Account, for that Cost Cost, for that specific year-month. The Actual column is the value of the transaction and Description is the description of the transcation. So for example, in January 2019 the company spent 60 dollars on salary and gas. And the budget was 100 - not 200. And in February 2019, 210 dollars was spent and the budget was 200.

I want to create a matrix, tha display the right budget values, but it sums all the values - January 200, and Februaray 600 dollars.

How can I do that, using Dax or Power Query and preferably , not creating a new table.

interkoubess · Answer

Hi&nbsp;Anonymous,&nbsp;Did you try in the case of the budget the average function, because it seems the budget amount is repeated...&nbsp;Let us know&nbsp;Ninter&nbsp;

anonymous · Answer

Hi&nbsp;Interkoubess, using the average only works if the the matrix is on the last drill level. Look the example:&nbsp;&nbsp;The right budget is 300 not 160. How can I do that?&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

interkoubess · Answer

Hi&nbsp;Anonymous,&nbsp;How&nbsp; the budget is populating?&nbsp;Could you please check this column formula based on the first date ( I don't know how the budget is populating),Budget_modified = IF(DAY('Table 0'[Date])=1,'Table 0'[Budget],0)In summary I make the budget equals to zero but the first day of the month.&nbsp;Let me know if it does not work.....&nbsp;Ninter

v-danhe-msft · Answer

Hi&nbsp;Anonymous,
Based on my test, I could not understand why the average value in '2019' shoud be 300? It seems that the total value in 2019 should be 800 and the average should be 800/5(date) and equal to 160. Could you please offer me more information about what you want to calculate?
&nbsp;
Regards,
Daniel He&nbsp;