Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Buget vs Actual on one single table

I have the following transactions table:

 

CCostAccountDateBudgetActualDescription
112019010110010Gas
112019010210050Salary
112019020120050Gas
1120190202200150Salary
112019020320010Gas

 

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.

image.png

 

 

 

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

10 REPLIES 10
avknishokpbi
Frequent Visitor

 

 

Try this dax measure

 

totalbudget =
Var budgetbymaxdate = CALCULATE(sum(Table1[Budget]),FILTER(Table1,Table1[Date]=ENDOFMONTH(Table1[Date])))
Return
budgetbymaxdate

 

 

image.png

Anonymous
Not applicable

@avknishokpbi,

 

It worked, but sorry, I thought it was not relevant, but I am using a date table to filter the sales table.

 

CalendarTable                                       Sales

Date      <----------------------------->  Date

Month                                                    CCenter

Day                                                         Account

Year                                                        Budget

                                                               Actual

                                                               Description

 

So using the measure that you said does not works when I use slicers with CalendarTable's columns.

@Anonymous

 

Try this DAX

 

TotalbudgetbyMonth =
var maxvalue = SUMX(SUMMARIZE(DateTable,DateTable[Month],"amt",MIN(Table1[Budget])),[amt])
return
maxvalue

 

Capture.PNGCapture2.PNG

Anonymous
Not applicable

@avknishokpbi, almost working.

image.png

 

 

When I use a drill down, it shows the smallest value intead of the sum.

Interkoubess
Solution Sage
Solution Sage

Hi @Anonymous,

 

Did you try in the case of the budget the average function, because it seems the budget amount is repeated...

 

Let us know

 

Ninter

 

Anonymous
Not applicable

Hi @Interkoubess, using the average only works if the the matrix is on the last drill level. Look the example:

image.png

 

 

The right budget is 300 not 160. How can I do that?

 

 

 

 

 

Hi @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?

 

Regards,

Daniel He 

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi @v-danhe-msft

 

I understood why the average is 160, but I do not want it. Looking on the sample dataset, the values of the budget get repeated for every sale. For example:

 

Dataset:

One sale on the day:

112019010110010Gas

 

Two sales on the day:

112019010110010Gas
112019010110050Gas

 

Four sales on the day:

112019010110010Gas
112019010110050Gas
112019020110050Gas
1120190201100150Gas

 

For the Cost Center 1 - Account 1 - 2019 January, independent  of the number of the sales, the budget for Gas is 100 dollars. So I want to display 100 dollars on matrix using drill down.

 

 

Hi @Anonymous,

 

How  the budget is populating?

 

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.

 

Let me know if it does not work.....

 

Ninter

Anonymous
Not applicable

@Interkoubess, most of time there are more than one sale per day.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.