DEFINE
VAR ParmPPO = @Parm_Parent_Project_Only
VAR Parm_Project_ID = @Parm_Project_ID
VAR Parm_Company = "KGPS"
VAR __Report2B = {("PRJ0000001.0001","200i",100.00,50),
("PRJ0000001.0001","200e",200.00,40),
("PRJ0000001.0003","200f",100.00,50)
}
VAR __Report1A =
FILTER (
ADDCOLUMNS (
SUMMARIZECOLUMNS (
'dimProject'[Project ID],
factProjectFinancials[Category],
KEEPFILTERS ( TREATAS ( { Parm_Company }, dimCompany[Company UC] ) ),
KEEPFILTERS ( TREATAS ( { "Posted Project Transactions" },factProjectFinancials[Table Source])),
"Total_Actual_Cost", [Project Posted Total Actual Cost]
),
"Forecast_Total_Cost_Amount", 0
),
(
Parm_Project_ID = "%" /* forces the DAX to run for ALL projects */
|| (
ParmPPO = "0"
&& CONTAINSSTRING ( 'dimProject'[Project ID], Parm_Project_ID )
) /* works as a LIKE without wildcards */
|| (
ParmPPO = "1"
&& PATHCONTAINS ( Parm_Project_ID, 'dimProject'[Project ID] )
) /* retuns the | delimited list entered (or single value)... i.e. parent project PRJ0000015 */
)
)
VAR __Report1B =
SELECTCOLUMNS (
__Report1A,
"Project ID", [Project ID],
"Category", [Category],
"Total Forecast Cost", [Forecast_Total_Cost_Amount],
"Total Actual Cost", [Total_Actual_Cost]
)
VAR Combine1_2 =
UNION(__Report1B,__Report2B)
VAR Grouped_1_2 =
GROUPBY (
Combine1_2,
[Project ID],
"Total Forecast Cost", SUMX ( CURRENTGROUP (), [Total Forecast Cost] ),
"Total Actual Cost", SUMX ( CURRENTGROUP (), [Total Actual Cost] )
)
EVALUATE Grouped_1_2
What is the final total for "PRJ0000001.0001" on "Total Forecast Cost"?
