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. 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.

2 Replies

  • 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 -