Trying to link many to 1 commentary to data table with differing levels of hierachy

[tatmaninov]

Would really appreciate some help on this problem I am working on, the more I try and do it the more I think I am over complicating it or maybe it is not possible.

Overview

Each week (not always exactly 7 days apart) project numbers are reported and these have various levels of hierarchy (BU, Category, Project, SubProject, Status etc.).

In its most simplest form what I want to do is compare the numbers from a previous week to the current week and show the difference (delta).  I then want to give users opportunity to share a reason for the difference between the numbers.  I intend to do this with a Writeback app and the reasons will be pre-defined for the user to select from a list for example (Inflation, Project delayed etc.), however the purpose of this post is really to ask for help on how to structure the tables and visuals, before I get to the app bit.  For now I create Reason table with some inputs, but I am not sure this is best way to do it.

Complications to consider;

• User can provide reason for either Project or SubProject (many SubProject to 1 project) or both, for example if a reason is provided for 1 SubProject and also at Project level, then the Project reason to apply to all SubProjects that do not have a reason.
• Each week to week delta may have more than 1 reason (many reasons to 1 delta)
• The delta period specified by the user may not be 1 week delta it may cover multiple weeks
• The delta is the delta of the whole Project/SubProject ignroing the project status (so is the sum of all the statuses), because of this I am not sure it is the right thing to do try and attach comments to main data table, so I created grouped tables that gives total of projects by Project and SubProject

For example I envisage user entering information like this;

Field	Data
Delta Start	[Dropdown listing dates]
Delta End	[Dropdown listing dates]
BU Level 1	[Dropdown listing BU's]
Category	[Dropdown listing Categories]
Project No.	[Dropdown listing Projects]
SubProject No.	[Dropdown listing SubProjects]
Year	[Dropdown listing Years]
Total Delta	CALCULATED VALUE: Total Delta related on selections above
Unexplained Delta	CALCULATED VALUE: Total Delta that has not already had a reason provided
Reason1	[User selects from dropdown]
Value1	User inputs value
Reason2 (optional)	[User selects from dropdown]
Value2	User inputs value
Reason2 (optional)	[User selects from dropdown]
Value3	User inputs value

I attach a demo file that I am working on that will hopefully illustrate what I am trying;

Demo pbix 

In this visual all it is working as intended to this point;

But as soon as I introduce reasons it seems to list every reason under every Project/Subproject and the values being reported are wrong.  I tried this two ways one by dragging column over and then again with a Measure.

I think I have made some mistake with relationships or something and maybe think I need to take completely different approach.  Would really appreciate any help to point me the right direction.

[amustafa]

Hi @tatmaninov ,

A couple of questions to be able to solution this the right way.

Where is your source data coming from? Is it some type of a database, ERP application, Spreadsheets etc?

In case of flat files, is it stored in a shared drive? e.g One Drive for Business, Google Drive etc?

Your end users who will be updating the reason, do they have Microsoft/Office 365 subscriptions to be able to use Power Apps (incase if you decide to build an app) Or can you push results back to an ERP system for users to update an exisitng or new records?

Does your company have Power BI online service licensing?

In the meantime, I have access to your Power BI file and sample data. I'll cretae an end-to-end soloution using Excel files in a OneDrive without using Power App for now.

[tatmaninov]

Thanks for reply @amustafa and making the effort to help.  Source data is coming from Excel stored on SharePoint. For the write back I don't think all colleagues will have full subscription so I would probably use it like this in order of preference;

1. Colleagues update themselves with app

2. I update on behalf of colleagues (during meeting)

3. Last resort I give access to source file (Excel file in SharePoint), but realise with this I lose some of the calculations that Power Bi will be doing.