TREATAS - reconciling the numbers


#1

Hi all
I am really confused what i am doing wrong using TREATAS.

Situations:
So i have used TREATAS doing a budget on “regional level” of 6% of sales for 2015.
So i have copied the power bi table into excell - and have checked this number for “Jan-2015” add up well and is correct! happy days!!

The problem:
So my budget allocation per month and year - breakdown looks fine (Reconciled in Excel)
However when I use the dax to get a daily level I am confused as to why the number for daily level is not fixed. i.e. Jan-2015 £255,745 / 31 = £8,249.

Total Budget (Region) = CALCULATE(SUM('Regional Budgets'[2016 Regionnal Budgets]),
 TREATAS(VALUES(Dates[MonthYearFull]),'Regional Budgets'[MonthYearFull]))

Budget Allocation (Region) =
VAR DaysInMonth = CALCULATE(COUNTROWS(Dates),ALL(Dates),VALUES(Dates[MonthYearFull]))
VAR ActualBudget = [Total Budget (Region)]
RETURN
IF(ISFILTERED(Dates[Date]),
DIVIDE(ActualBudget,DaysInMonth,0),[Total Budget (Region)])

#2

Have you watched any of the training video’s on TREATAS? Here is a video to help guide you.

Enterprise%20DNA%20Expert%20-%20Small


#3

Yea
Done that …
That was the first thing i did.


#4

Can I get a copy of the PBIX to have a further look.

Thanks
Jarrett

Enterprise%20DNA%20Expert%20-%20Small


#6

hi
file is too big and cannot upload. - ive just taken all the other stuff out to redue space still cannot upload.

no worries

shrik


#7

Hi @ojones

I have attached the file.
Thank you so much as well for having a look!
In order to get budget as a daily amount I have followed the way Sam has done this in one of his learning summits.

I have at08.Upload.pbix (1014.5 KB)


#8

May 2018 Learning Summit Demo.pbix (907.8 KB)
Here is the original file that Sam created in case you don’t have it. I think the issue with this not working is related to you not having an order date for every day of the year. I will continue to look into your PBIX.

Thanks
Jarrett

Enterprise%20DNA%20Expert%20-%20Small


#9

Hi Jarrett.
I see what you mean.
So to give you the heads up -the data source was the “Order Supermarket” this has 58,000 rows. so could not uplod into forum. Hence why i had to take out a lot of data (in excel) to make file lighter.

So the dates might not be in order as of now but before they were - i had still had the daily issue.
Just lookin at the file now - “Budget Allocation for Jan 2015” was £44,994.
So i would still expect the daily dates to show £1,451 (44,994 / 31)

Thanks - appreciated in advance.

Shrik