Hello.
We've got a project hierarchy. The Project IDs are like so:
1 Department of XXX
1.1 Requirements Gathering $20
1.1.1 Finance $15
1.1.2 Field Services $15
1.2 System Implementation $10
1.2.1 Configuration $5
1.2.1.1 Report Customisation $5
1.2.2 Data Migration $30
Important points:
- Transactions can be entered against any project ID. New child projects can be created even if their parent projects have had transactions entered against it.
- Any project ID's transaction amount or balance DOES NOT include the transactions of the child IDs nested under it
- e.g. If 1.1 reports a $20 balance, that $20 is a sum of its own transactions. There is no drilldown of that $20 into 1.1.1 and 1.1.2. The children 1.1.1 and 1.1.2 will have their own balances that do not contribute to that $20. There is no aggregation or rollup.
- We need to display that aggregation in the PowerBI report (Requirement A). We also need to display single selected project ID and its own balance (Requirement B). We need a toggle to switch between the two.
- e.g Requirement A: select 1.1 in slicer, display report as if 1.1 and 1.1.1 and 1.1.2 are all selected, $50.
- e.g Requirement B: select 1.1 in slicer, display report for 1.1 only, $20.
- We need multi-select for both requirements.
- e.g Requirement A: select 1.1 and 1.2 in slicer, display report as if 1.1, 1.1.1, 1.1.2, 1.2, 1.2.1, 1.2.1.1, 1.2.2 are all selected, $100.
- e.g Requirement B: select 1.1 and 1.2 in slicer, display report for 1.1 and 1.2 only, $30.
Problems:
- PowerBI slicer works for Requirement B.
- PowerBI slicer display is not ideal for Requirement A.
- To turn the slicer into a hierarchy slicer, we need to flatten the hierarchy by adding columns that split the ID into their generations. This means for each generation a column needs to be manually created, and not all projects are the same number of generations deep. Some projects will have BLANK under Level 4 if they don't have great-grandchildren. These BLANK values will show up in the slicer. To hide them, we can set the filter for each level to "is not blank". However doing this to the Level 4 filter causes the entire project family to not show up if they don't have a child project 4 generations deep.
- When 1.1.1 is selected, it displays 1 (Level 1) + 1.1 (Level 2) + 1.1.1 (Level 3). We just want the selected 1.1.1 to display.
- What is the best way to toggle between both Requirements?
