Forum Discussion

RSSapre's avatar
RSSapre
Frequent Visitor
5 years ago

Budget tool data model

I am working on a budget tool to track budget vs. Actuals. I have 2 fact tables, one for the budget and one for actuals. The grain is location, dept, acct and date. Everything is working fine so far....
amitchandak's avatar
amitchandak
Super User
5 years ago

RSSapre , This model seems fine the only reason the grand total is coming wrong if your measure usages row context. In such case you have use values or summarize to get the correct total

 

sumx(values(Date[Min-Year]), [Measure])

or

sumx(summarize(Table, Dept[Dept],Date[Month-year], "_1",[measure]),[_1])

 

can You share the calculation.? Can you share sample data and sample output in table format?

RSSapre's avatar
RSSapre
Frequent Visitor
5 years ago

Thanks Amit. Here is a sample measure - Plan:=CALCULATE(SUM(FactPlan[Amount])) and I havesimilar for Actuals. These are plain and simple. I creates a sample data and PBIX and it appears to be working. I think the direction of the relationship matter. I thought it is something related to Azure Analysis service or the way I am building my tabular model. my PBIX works with sample data. After building sample data and pbix for you I made few changes to my tabular model and report is working as expected, however, I will continue to test it. I need to extend this to implement RLS for users in department or users related to department and by location so that logged in person can see only data that they are suppose to see. Any pointers on that will be helpful.

I am more that happy to share my sample data and pbix file, however, I do not see an option here to attach those. Copying some sample data and output here.

Budget - 

BMTHDeptAmountLocId
1/1/2020102001
2/1/2020102001
3/1/2020102001
4/1/2020102001
5/1/2020102001
6/1/2020102001
7/1/2020102001
8/1/2020102001
9/1/2020102001
10/1/2020102001
11/1/2020102001
12/1/2020102001
1/1/2020202001
2/1/2020202001
3/1/2020202001
4/1/2020202001
5/1/2020202001
6/1/2020202001
7/1/2020202001
8/1/2020202001
9/1/2020202001
10/1/2020202001
11/1/2020202001
12/1/2020202001
2/1/2020301002
3/1/2020301002
4/1/2020301002
5/1/2020301002
6/1/2020301002
7/1/2020301002
8/1/2020301002
9/1/2020301002
10/1/2020301002
11/1/2020301002
12/1/2020301002
1/1/2020401002
2/1/2020401002
3/1/2020401002
4/1/2020401002
5/1/2020401002
6/1/2020401002
7/1/2020401002
8/1/2020401002
9/1/2020401002
10/1/2020401002
11/1/2020401002
12/1/2020401002

 

Actuals - 

AMTHDeptAmountLocId
1/1/2020101001
2/1/2020101001
3/1/2020101001
4/1/2020101001
5/1/2020101001
6/1/2020101001
7/1/2020101001
1/1/2020201001
2/1/2020201001
3/1/2020201001
4/1/2020201001
5/1/2020201001
6/1/2020201001
7/1/2020201001
1/1/2020301001
2/1/2020301001
3/1/2020301001
4/1/2020301001
5/1/2020301001
6/1/2020301001
7/1/2020301001
1/1/2020401001
2/1/2020401001
3/1/2020401001
4/1/2020401001
5/1/2020401001
6/1/2020401001
7/1/2020401001
1/1/202010202
2/1/202010202
3/1/202010202
4/1/202010202
5/1/202010202
6/1/202010202
7/1/202010202
1/1/202020202
2/1/202020202
3/1/202020202
4/1/202020202
5/1/202020202
6/1/202020202
7/1/202020202
1/1/202030202
2/1/202030202
3/1/202030202
4/1/202030202
5/1/202030202
6/1/202030202
7/1/202030202
1/1/202040202
2/1/202040202
3/1/202040202
4/1/202040202
5/1/202040202
6/1/202040202
7/1/202040202

 

Dept - 

DeptIDDeptName
10Dept 1
20Dept 2
30Dept 3
40

Dept 4

 location - 

LocIdLocation
1WA
2NY
3TX

 

Dept Owner - 

DeptIDLocIdOwner
101A
201A
301B
401C
102A
202D
302E
402F
103F
203C
303G
403G

 

For DimDate, I used calendarauto() and added custom columns. Here is my output -