I need to calculate project budget in my table visual which should displays its total budget completely irrespective of any Period Year filter applied by the user. Below measure is working fine for total but not working on granular level in table as expected.
As, I'm showing other values such actuals and forecast in the table, I can't remove interaction directly.
So, I tried using Removefilters but it is also not working properly. When the Period Year slicer has all values selected, the Project Budget measure shows correct values in all granular rows.
But when I apply any filter it will show some descrepancy.
| Bulk graphic | Type | Data Source | Provider Type | Supplier | Operation Type | Operation Type Name | Topic Owner | Project ID | Topic Manager | Topic Controller | ARE Owner | ARE Owner unconsolidated | Net Cost EUR | M Net Cost EUR unconsolidated |
| Project | Budget | Provider | I | SuppX | 1 | Consulting | Owner_A | PA001 | Manager_X | Controller_1 | ARE_X | ARE_Y | 1000 | 1000 |
| Project | Budget | Provider | I | SuppX | 1 | Consulting | Owner_A | PA001 | Manager_X | Controller_1 | ARE_X | ARE_Y | 1000 | 1000 |
| Project | Budget | Provider | I | SuppX | 1 | Consulting | Owner_A | PA001 | Manager_X | Controller_1 | ARE_X | ARE_Y | 1000 | 1000 |
| Project | Actual | Provider | I | SuppX | 1 | Consulting | Owner_A | PA001 | Manager_X | Controller_1 | ARE_X | ARE_Y | 500 | 500 |
| Project | Budget | Provider | E | SuppY | 2 | Software | Owner_B | PB002 | Manager_Y | Controller_2 | ARE_Y | ARE_Z | 2000 | 2000 |
| Project | Budget | Provider | E | SuppY | 2 | Software | Owner_B | PB002 | Manager_Y | Controller_2 | ARE_Y | ARE_Z | 2000 | 2000 |
| Project | Budget | Provider | E | SuppY | 2 | Software | Owner_B | PB002 | Manager_Y | Controller_2 | ARE_Y | ARE_Z | 2000 | 2000 |
| Project | Budget | Provider | E | SuppY | 2 | Software | Owner_B | PB002 | Manager_Y | Controller_2 | ARE_Y | ARE_Z | 2000 | 2000 |
| Project | Actual | Provider | E | SuppY | 2 | Software | Owner_B | PB002 | Manager_Y | Controller_2 | ARE_Y | ARE_Z | 1000 | 1000 |
| Project | Budget | Provider | S | SuppZ | 3 | Hardware | Owner_C | PC003 | Manager_Z | Controller_3 | ARE_Z | ARE_X | 3000 | 3000 |
| Project | Budget | Provider | S | SuppZ | 3 | Hardware | Owner_C | PC003 | Manager_Z | Controller_3 | ARE_Z | ARE_X | 3000 | 3000 |
| Project | Budget | Provider | S | SuppZ | 3 | Hardware | Owner_C | PC003 | Manager_Z | Controller_3 | ARE_Z | ARE_X | 3000 | 3000 |
| Project | Actual | Provider | S | SuppZ | 3 | Hardware | Owner_C | PC003 | Manager_Z | Controller_3 | ARE_Z | ARE_X | 1500 | 1500 |
| Project | Actual | Provider | I | SuppA | 1 | Consulting | Owner_D | PD004 | Manager_A | Controller_4 | ARE_X | ARE_Y | 100 | 100 |
| Project | Actual | Provider | I | SuppA | 1 | Consulting | Owner_D | PD004 | Manager_A | Controller_4 | ARE_X | ARE_Y | 200 | 200 |
| Project | Budget | Provider | I | SMO_088888 | 1 | Consulting | Owner_E | PE005 | Manager_B | Controller_5 | ARE_Y | ARE_Z | 5000 | 5000 |
| Project | Budget | Provider | I | SMO_088888 | 1 | Consulting | Owner_E | PE005 | Manager_B | Controller_5 | ARE_Y | ARE_Z | 5000 | 5000 |
| Project | Budget | Provider | I | SuppB | RDCB | R&D Budget | Owner_F | PF006 | Manager_C | Controller_6 | ARE_Z | ARE_X | 6000 | 6000 |
| Project | Budget | Provider | I | SuppB | RDCB | R&D Budget | Owner_F | PF006 | Manager_C | Controller_6 | ARE_Z | ARE_X | 6000 | 6000 |
</>
Project Budget =
VAR SelectedConsolidation = SELECTEDVALUE('Consolidation_flag'[Consolidation view - Copy])
RETURN
SWITCH(
TRUE(),
SelectedConsolidation = "Switch to ARE Consolidated view",
COALESCE(
CALCULATE(
SUM('Final data'[Net Cost EUR]),
'Final data'[Bulk graphic] = "Project",
'Final data'[Type] = "Budget",
'Final data'[Data Source] = "Provider",
'Final data'[Provider Type] IN {"I", "E", "S"},
NOT 'Final data'[Supplier] IN {"SMO_088888"},
NOT 'Final data'[Operation Type] IN {"RDCB", "9"},
REMOVEFILTERS('Final data'[Period Year])
),
0
),
SelectedConsolidation = "Switch to World Consolidated view",
COALESCE(
CALCULATE(
SUM('Final data'[M Net Cost EUR unconsolidated]),
'Final data'[Bulk graphic] = "Project",
'Final data'[Type] = "Budget",
'Final data'[Data Source] IN {"Provider", "Receiver"},
NOT 'Final data'[Supplier] IN {"SMO_088888"},
NOT 'Final data'[Operation Type] IN {"RDCB", "9"},
REMOVEFILTERS('Final data'[Period Year])
),
0
),
0 // Default if consolidation flag is blank or unmatched
)
</>
