Budget tool data model

[RSSapre]

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. I have two questions about modeling 2 situations.

1. There is a report that shows budget vs. Actuals for each dept owner.

2. Implement RLS for people based on association with Dept.

I have created a bridge table for Dept Owner which has location,dept and owner keys. there can be multiple owners based on the location.BT Data Model

I created relationship between Facts and Dims, bridge table and dims. On my report, my grand total is not appearing correct. It shows sum for entire year. I have slicers for year and month. my report has Owner, Dept, Account and when I drill down, the numbers appear to be correct. Only when I collapse the hierarchy or at the Owner level, the numbers are not getting filtered. It works sometime in by test model, but not on real data where I am using AAS. 

I am wondering if I am missing anything. Will the same model work for RLS?

Any help is appreciated.

[amitchandak]

@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]

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 - 

BMTH	Dept	Amount	LocId
1/1/2020	10	200	1
2/1/2020	10	200	1
3/1/2020	10	200	1
4/1/2020	10	200	1
5/1/2020	10	200	1
6/1/2020	10	200	1
7/1/2020	10	200	1
8/1/2020	10	200	1
9/1/2020	10	200	1
10/1/2020	10	200	1
11/1/2020	10	200	1
12/1/2020	10	200	1
1/1/2020	20	200	1
2/1/2020	20	200	1
3/1/2020	20	200	1
4/1/2020	20	200	1
5/1/2020	20	200	1
6/1/2020	20	200	1
7/1/2020	20	200	1
8/1/2020	20	200	1
9/1/2020	20	200	1
10/1/2020	20	200	1
11/1/2020	20	200	1
12/1/2020	20	200	1
2/1/2020	30	100	2
3/1/2020	30	100	2
4/1/2020	30	100	2
5/1/2020	30	100	2
6/1/2020	30	100	2
7/1/2020	30	100	2
8/1/2020	30	100	2
9/1/2020	30	100	2
10/1/2020	30	100	2
11/1/2020	30	100	2
12/1/2020	30	100	2
1/1/2020	40	100	2
2/1/2020	40	100	2
3/1/2020	40	100	2
4/1/2020	40	100	2
5/1/2020	40	100	2
6/1/2020	40	100	2
7/1/2020	40	100	2
8/1/2020	40	100	2
9/1/2020	40	100	2
10/1/2020	40	100	2
11/1/2020	40	100	2
12/1/2020	40	100	2

Actuals - 

AMTH	Dept	Amount	LocId
1/1/2020	10	100	1
2/1/2020	10	100	1
3/1/2020	10	100	1
4/1/2020	10	100	1
5/1/2020	10	100	1
6/1/2020	10	100	1
7/1/2020	10	100	1
1/1/2020	20	100	1
2/1/2020	20	100	1
3/1/2020	20	100	1
4/1/2020	20	100	1
5/1/2020	20	100	1
6/1/2020	20	100	1
7/1/2020	20	100	1
1/1/2020	30	100	1
2/1/2020	30	100	1
3/1/2020	30	100	1
4/1/2020	30	100	1
5/1/2020	30	100	1
6/1/2020	30	100	1
7/1/2020	30	100	1
1/1/2020	40	100	1
2/1/2020	40	100	1
3/1/2020	40	100	1
4/1/2020	40	100	1
5/1/2020	40	100	1
6/1/2020	40	100	1
7/1/2020	40	100	1
1/1/2020	10	20	2
2/1/2020	10	20	2
3/1/2020	10	20	2
4/1/2020	10	20	2
5/1/2020	10	20	2
6/1/2020	10	20	2
7/1/2020	10	20	2
1/1/2020	20	20	2
2/1/2020	20	20	2
3/1/2020	20	20	2
4/1/2020	20	20	2
5/1/2020	20	20	2
6/1/2020	20	20	2
7/1/2020	20	20	2
1/1/2020	30	20	2
2/1/2020	30	20	2
3/1/2020	30	20	2
4/1/2020	30	20	2
5/1/2020	30	20	2
6/1/2020	30	20	2
7/1/2020	30	20	2
1/1/2020	40	20	2
2/1/2020	40	20	2
3/1/2020	40	20	2
4/1/2020	40	20	2
5/1/2020	40	20	2
6/1/2020	40	20	2
7/1/2020	40	20	2

Dept - 

DeptID	DeptName
10	Dept 1
20	Dept 2
30	Dept 3
40	Dept 4

 location - 

LocId	Location
1	WA
2	NY
3	TX

Dept Owner - 

DeptID	LocId	Owner
10	1	A
20	1	A
30	1	B
40	1	C
10	2	A
20	2	D
30	2	E
40	2	F
10	3	F
20	3	C
30	3	G
40	3	G

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